Forum Discussion

tamasungor's avatar
tamasungor
Regular Visitor
3 years ago

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"

Currently, what i get is:

a8


Thanks, any advice.
Tomi

2 Replies