Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have 4 columns: TV channel, Show, Country, Language
I am trying to display in a card the TV channel with the second highest number of TV shows count (for example I would like to display "CNN" if it's the TV channel with the second higher count of TV shows). I apply two slicers on that card based on country and language.
My current approach has been to compute a RANKX() measure on TV channel based on COUNT(TV show). Then, I tried to filter my card with the TV channel based on that measure, setting Rank = 2 but I cannot interact with the filter (neither click on it nor write anythign in it).
My workaround at the moment is to use a table. I don't know why but in a table I can filter based on my rank while in a card I cannot. Nonetheless, the layout is not super clean as I have to hide the column header.
So my question is, do you have any idea on how to get the second top TV channel in term of TV show count?
Thanks for your help,
Solved! Go to Solution.
Hey @Anonymous ,
this is how my sample data looks:
I created 2 measures:
channel ranked =
RANKX(
ALLSELECTED('Table'[Channel])
, CALCULATE(
SUM('Table'[Count([TV Show]])])
)
)
and
channel filtered by rank =
CONCATENATEX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
VALUES('Table'[Channel])
, 'Table'[Channel]
)
, "rk" , [channel ranked]
)
, [rk] = 2
)
, [Channel]
)
I used the 2nd measure on the card visual:
Hopefully, this is what you are looking for.
Regards,
Tom
Hey @Anonymous ,
this is how my sample data looks:
I created 2 measures:
channel ranked =
RANKX(
ALLSELECTED('Table'[Channel])
, CALCULATE(
SUM('Table'[Count([TV Show]])])
)
)
and
channel filtered by rank =
CONCATENATEX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
VALUES('Table'[Channel])
, 'Table'[Channel]
)
, "rk" , [channel ranked]
)
, [rk] = 2
)
, [Channel]
)
I used the 2nd measure on the card visual:
Hopefully, this is what you are looking for.
Regards,
Tom
Thanks a lot, it works perfectly.
How would you do it to apply the same solution but to display the COUNT(TV show) instead?
BEst,
Olivier
Hey @Anonymous ,
I changed the 2 nd measure a little:
channel filtered by rank =
CONCATENATEX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
VALUES('Table'[Channel])
, 'Table'[Channel]
)
, "cnt" , CALCULATE(SUM('Table'[Count([TV Show]])]))
, "rk" , [channel ranked]
)
, [rk] = 2
)
, [Channel] & ": " & [cnt]
)
I added the column [Count(TV Show)] using CALCULATE(SUM(...)) if the count in your example already is a measure just use the measure reference [measure] name inside the ADDCOLUMNS(SUMMARIZE(...), "cnt", ...
This allows to use this column inside the CONCATENATEX. In my example I still use the channel column and a little text snippet (": "). My Card visual will look like this:
Hopefully, this is what you are looking for.
Regards,
Tom
Hello,
Thanks. It works almost perfectly. Nontheless, I don't know why, it prints twice the value. instead of 2065 I have 20652065 and instead of 10.5 I have 10.510.5. Please find below the query I used.
Hey @Anonymous ,
this is because there are 2 rows that satisfy the condition
[rk] = 2
This means you have to check the rank, and maybe you have to use a 2nd summarize that uses the FILTER(...) as input.
You can also consider to change the CONCATENATEX function to SUMX or AVERAGEX, whatever meets your use case
Hopefully, this provides some ideas.
Regards,
Tom
@Anonymous ,
assume this is rank measure
City Rank = RANKX(all(Geography[City]),[Sales])
Rank 2nd top = CALCULATE(if([City Rank]=2,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank 2nd top = sumx(VALUES(Geography[City]),if([City Rank]=2,[Sales],BLANK()) )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )
Also refer
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |