I have uploaded a MySQL database to PowerBI; this is how the data model looks like:
I’d like to reproduce this MySQL query with DAX:
SELECT concat(substring(ano, 1, 3), 0) AS decada, nombre_pais, nombre_ciudad, count(nombre_ciudad) AS numero FROM paises NATURAL JOIN ciudades NATURAL JOIN autores NATURAL JOIN publican NATURAL JOIN discos NATURAL JOIN canciones NATURAL JOIN listas_spotify GROUP BY decada, nombre_ciudad
It calculates the number of times every city (from table
ciudades) appears in the database depending on the number of songs (from table
canciones) by bands (from table
autores) located in that city, by decade (from table
listas_spotify, that includes a date parameter).
numero, I get a table like this:
decada, nombre_pais, nombre_ciudad, numero 1980, Inglaterra, Londres, 23 1990, Inglaterra, Londres, 15 1980, Inglaterra, Mánchester, 11 2000, EEUU, Austin, 11 2000, EEUU, Nueva York, 10 1980, EEUU, Boston, 9 1980, EEUU, Nueva York, 8 1990, Inglaterra, Mánchester, 7 1990, Inglaterra, Oxford, 7 ...
If I directly upload this table to PowerBI, it’s easy to create a graph like this (filtered just for five major cities):
But I have no idea how to get it from the PowerBI data model using DAX. I don’t expect a full solution to my issue, just to get to know how to relate tables in order to count the number of items from one depending on parameters from another, so I can recreate my SQL query with DAX syntax.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I’d create a merge table using PowerQuery with all the fields you need and then do a Group By