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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
abhi139
Frequent Visitor

Count of values in new table multiple columns

Hi,

I have below table in power BI

           MatchA MatchB MatchC

Row1  SAME DIFFERENT BLANK

Row2  SAME SAME BLANK

Row3  SAME SAME DIFFERENT

I need to create report output like below:

 

Sr.       SAME DIFFERENT BLANK

MatchA 3     0              0

MatchB 2      1               0

MatchC 0      1            2 

 

Please advise how can I achieve this in power BI and also the option to drill down to records based on criteria (e.g. Clicking on 3 will show all 3 records and so on)

Thanks for your support. 

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @abhi139

 

Try this in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUQp29HUFUi6ebm6uQa5+IUC2k4+jn7dSrA5YjRFCDZRCkTbGkEaYFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ROW = _t, MatchA = _t, MatchB = _t, MatchC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ROW", type text}, {"MatchA", type text}, {"MatchB", type text}, {"MatchC", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ROW"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Try measure as:

Measure 2 = 
CALCULATE(
    COUNT('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Attribute]=MAX('Table'[Attribute])
))

 Here is the output:

v-xulin-mstf_0-1619593492937.png

The pbix file is attached.

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-xulin-mstf
Community Support
Community Support

Hi @abhi139

 

Try this in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUQp29HUFUi6ebm6uQa5+IUC2k4+jn7dSrA5YjRFCDZRCkTbGkEaYFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ROW = _t, MatchA = _t, MatchB = _t, MatchC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ROW", type text}, {"MatchA", type text}, {"MatchB", type text}, {"MatchC", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ROW"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Try measure as:

Measure 2 = 
CALCULATE(
    COUNT('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Attribute]=MAX('Table'[Attribute])
))

 Here is the output:

v-xulin-mstf_0-1619593492937.png

The pbix file is attached.

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

VijayP
Super User
Super User

@abhi139 

I suggest to unpivot the Table in Power Query and create a table to get the result 

follow this video to understand that https://drive.google.com/file/d/1Yk_neCzTsKlJFMGRVuRqVz4fFOG9wAJl/view?usp=sharing




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


HI @VijayP 

 

Many thanks for the quick solution - this is very close to what i was looking for.

 

However, Is there any way i can drill -down in the matrix to see actual data? i.e. if i click/drill down on 2 (Blanks) for Match C, it shows the Row1 and Row2 table record?

 

Actually Match columns are the comparison between other columns (i didnt added those columns in my oriingal question).

 

the objective is to show the differences (count of SAME,BLANKS,DIFFERENT) as well as ability to view the records of the count shown in the matrix.

 

Many thanks once again.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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