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 | C...
Need Help
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]
        )
    )
)