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
tamasungor
Regular Visitor

DAX count occurances in multiple column

Dear Community!

I have the next sample data set: 

datenumbers1234
2022.01.011abcd
2022.01.012bcde
2022.01.023fghu
2022.01.024assd
2022.01.035bdxs
2022.01.036vnfo
2022.01.047dtup
2022.01.048kuiő
2022.01.059tupá
2022.01.0510uiőő
2022.01.0611htup
2022.01.0612tuiő
2022.01.0713poiú

if a user select a letter (for example letter "a") from the slicer, the user will get the following table (expected results):

a2
b1
s2
c1
d2

i tried the following measure, but it seems it doesn't work properly:

 
count =
var filt_ = filters('unique'[unique])
var calc = DISTINCT(
        SELECTCOLUMNS(
            UNION(
                CALCULATETABLE(all(items),FILTER(items, items[1] = filt_)),
                CALCULATETABLE(all(items),FILTER(items, items[2] = filt_)),
                CALCULATETABLE(all(items),FILTER(items, items[3] = filt_)),
                CALCULATETABLE(ALL(items),FILTER(items, items[4] = filt_))),
                "id",items[numbers]))
var calc_ = CALCULATETABLE(all(items),FILTER(items, items[numbers] in calc))

return
COUNTROWS(
        UNION(SELECTCOLUMNS(items,"id",items[1]),
SELECTCOLUMNS(items,"id",items[2]),
SELECTCOLUMNS(items,"id",items[3]),
SELECTCOLUMNS(items,"id",items[4])
     ))

--------------------------------------------------------------
table relationships:
list table: tried multiple connection type, that's the last saved one, manually created table,

unique: dax created table (unique items from table items (distinct(union(items[1],items[2],items[3],items[4]))).
items: "fact table"
picture.png
Currently, what i get is:

a8


Thanks, any advice.
Tomi

2 REPLIES 2
Greg_Deckler
Super User
Super User

@tamasungor Not sure I am reading this correctly but I believe you want this: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks for the link. I checked the link, but currently i don't see, how can i apply in my case.
in the following link a sample data is available: Link 

Thank you any advice

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.

Top Solution Authors