cancel
Showing results for 
Search instead for 
Did you mean: 
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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors