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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SBePicas
Regular Visitor

Counting column items based on column selection

I have created three independent columns

columns.jpg

What I'd like to do is to select a column and get the number of items.  For exmple if I select Col1, I get 3 but selecting COL3 would get 6.  

May be using a measure and drop it in a card...

 

Many thanks in advance.

1 ACCEPTED SOLUTION

HI @SBePicas,

I'd like to suggest you create a new table with three field names and use it as source of slicer.

NewTable =
DATATABLE ( "ColName", STRING, { { "Col1" }, { "Col2" }, { "Col3" } } )

After these steps, you can create a measure formula with switch function to check current selected value and redirect to different calculation expressions.

 

DC based on selection =
VAR selected =
    SELECTEDVALUE ( NewTable[ColName] )
RETURN
    SWITCH (
        selected,
        "Col1", COUNTROWS ( VALUES ( Table1[Col1] ) ),
        "Col2", COUNTROWS ( VALUES ( Table1[Col2] ) ),
        "Col3", COUNTROWS ( VALUES ( Table1[Col3] ) ),
        COUNTROWS ( VALUES ( Table1[Col1] ) )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

hi @SBePicas ,

 

What do you mean by "independent"?

Are they from the same table? If they are from different tables, are the tables related?

Sorry, still learning the proper terminology.

I have one table with 3 columns.  Each field (column) is dragged into the report editor and thus I have three visuals in the report editor.

HI @SBePicas,

I'd like to suggest you create a new table with three field names and use it as source of slicer.

NewTable =
DATATABLE ( "ColName", STRING, { { "Col1" }, { "Col2" }, { "Col3" } } )

After these steps, you can create a measure formula with switch function to check current selected value and redirect to different calculation expressions.

 

DC based on selection =
VAR selected =
    SELECTEDVALUE ( NewTable[ColName] )
RETURN
    SWITCH (
        selected,
        "Col1", COUNTROWS ( VALUES ( Table1[Col1] ) ),
        "Col2", COUNTROWS ( VALUES ( Table1[Col2] ) ),
        "Col3", COUNTROWS ( VALUES ( Table1[Col3] ) ),
        COUNTROWS ( VALUES ( Table1[Col1] ) )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
sevenhills
Super User
Super User

 

Distinct count = 
VAR _val1 = VALUES(Tablename[Col1])
VAR _val2 = VALUES(Tablename[Col2])
VAR _val3 = VALUES(Tablename[Col3])
VAR _union = DISTINCT(UNION(_val1,_val2, _val3))
RETURN COUNTROWS(_union)

 

 

Try this!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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