Counting items from a table based on the number of appearances in another one

I have uploaded a MySQL database to PowerBI; this is how the data model looks like:

Counting items from a table based on the number of appearances in another one

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).

Ordering by 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):

Counting items from a table based on the number of appearances in another one

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.

Answers:

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.

Method 1

I’d create a merge table using PowerQuery with all the fields you need and then do a Group By


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x