Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Counting Repeated Details

Hi  all,

 

Sample file: Sample PBIX

 

I have a table like this,

 

new3.PNG

 

I would like to show a Card visual like this,

new2.PNG

 

If I select an ID in slicer as "1", I need to have values shown on the card. Please ref the below screenshot,


NEW.PNG

 

How can I acheive the above result?

 

Thanks in Advance.

1 ACCEPTED SOLUTION

@Anonymous 

Edited

Create the measure below and place it in a  card visual

Check it out in this file

 

NewMeasure =
VAR _Total =
    COUNT ( Sales[Detail] )
VAR _AuxTable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales; Sales[ID]; Sales[Detail] );
        "_Count"; CALCULATE ( COUNT ( Sales[Detail] ) )
    )
VAR _MaxCount =
    MAXX ( _AuxTable; [_Count] )
VAR _TotalMax =
    SUMX ( FILTER ( _AuxTable; [_Count] = _MaxCount ); [_Count] )
VAR _OutputText =
    CONCATENATEX (
        FILTER ( _AuxTable; [_Count] = _MaxCount );
        [_Count] & " - " & [Detail];
        UNICHAR ( 10 )
    )
VAR _FirstLineText = _TotalMax & "/" & _Total
VAR _AllText =
    _FirstLineText & UNICHAR ( 10 ) & _OutputText
RETURN
    _AllText

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Can the RANKX function will work for this kind of problem?

@Anonymous 

 

How is Maximum Detail Value computed?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi Zubair,
The maximum detail is the max count of texts in detail column. Here, in this example, the slicer is showing ID=2, for this selection, we need to find out the maximum of Detail column.
So, here, the repeated count is Low Price which is two times. Hence, MAX value is = 2.


New4.png

 

 

@Anonymous 

Edited

Create the measure below and place it in a  card visual

Check it out in this file

 

NewMeasure =
VAR _Total =
    COUNT ( Sales[Detail] )
VAR _AuxTable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales; Sales[ID]; Sales[Detail] );
        "_Count"; CALCULATE ( COUNT ( Sales[Detail] ) )
    )
VAR _MaxCount =
    MAXX ( _AuxTable; [_Count] )
VAR _TotalMax =
    SUMX ( FILTER ( _AuxTable; [_Count] = _MaxCount ); [_Count] )
VAR _OutputText =
    CONCATENATEX (
        FILTER ( _AuxTable; [_Count] = _MaxCount );
        [_Count] & " - " & [Detail];
        UNICHAR ( 10 )
    )
VAR _FirstLineText = _TotalMax & "/" & _Total
VAR _AllText =
    _FirstLineText & UNICHAR ( 10 ) & _OutputText
RETURN
    _AllText

 

Anonymous
Not applicable

Thank you for your reply. This solution is not dynamic hence gives worng values.
How can we count the repeated values on a single column?

Hi @Anonymous ,

 

Have tested with AlB 's suggestion, it returns dynamical result as expected. If it doesn't meet your requirement, what is your desire output?

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.