Forum Discussion

JpSantos_'s avatar
JpSantos_
Regular Visitor
4 years ago

DAX Query

Hello , I´m a bit new to Power Query and would love some help with achieving the requirements for a certain dashboard.

I have a Requests Table where I have the attributes:
RequestId | Continent | Country | City 
1                 Europe       Spain      Madrid
2                 Europe       Spain      Madrid
3                 Europe       France    Paris

4                 Europe       France    Paris
5                 Europe       Spain      Valencia

6                 America      USA       Madrid

I would like to have a ring chart with only the top 2 'places' that have made requests, where 'places' are the concatenantion of Continent | Country | City. For this small example the ring chart would have 2 (count of) requests for Europe | Spain | Madrid, and 2 (count of) requests for Europe | France | Paris. The data import mode is made by DirectQuery.

Thank you.

Need Help

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi JpSantos_ 

     

    I presume you want this in DAX as that is the topic name?  You mention Power Query, which is different to DAX.

     

    Download this PBIX file with solution.

     

    To do what you want, create a column in your data table that concatenates the continent, country and city

    Place = [Continent] & " | " & [Country] & " | " & [City]

     

    Then create a new table with TOPN that lists just the top 2 

    Top2 = 

TOPN(
    2,
    SUMMARIZE(
        'TableRaw',
        'TableRaw'[Place], 
        "Requests",
        COUNT('TableRaw'[Request ID])
    ), 
    [Requests]
)

     

    Then create your visual from the new table

     

     

    Regards

     

    Phil

     

    • JpSantos_'s avatar
      JpSantos_
      Regular Visitor
      4 years ago

      Hi PhilipTreacy 

      Thanks for the repply!
      Yes I would want the solution in DAX. The problem is that the import mode is in DirectQuery ( in fact it is an Hybrid Table) and it doesn´t let me create calculated columns that way. So I'm not sure how I can achieve concatenating the "Place" without calculated columns. Any ideias?

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Here is a way:
        (My table is named 'Locations')
        Using these measures:

         

        Count Locations =
CALCULATE (
    COUNTROWS ( Locations ),
    ALLEXCEPT (
        Locations,
        Locations[Continent],
        Locations[Country],
        Locations[City]
    )
)

        RANK Locations =
RANKX ( ALL ( Locations ), [Count Locations],, DESC, SKIP )

        Measure for chart = 
IF([RANK Locations] <=2, [Count Locations])

         

         

         

        or if you'd rather only have it in one measure:

         

        Top 2 =
IF (
    RANKX (
        ALL ( Locations ),
        CALCULATE (
            COUNTROWS ( Locations ),
            ALLEXCEPT (
                Locations,
                Locations[Continent],
                Locations[Country],
                Locations[City]
            )
        ),
        ,
        DESC,
        SKIP
    ) <= 2,
    CALCULATE (
        COUNTROWS ( Locations ),
        ALLEXCEPT (
            Locations,
            Locations[Continent],
            Locations[Country],
            Locations[City]
        )
    )
)