Help with DAX

[Anonymous]

Hello Experts - in continuation on the below solution, there is a slightly different requirement, looking forward for your suggestions, as this is kind of urgent.

Solved: Re: Compare two excel sheet and calculate how many... - Microsoft Power BI Community

the solution worked fine, now got slightly different requirement. There can be Apps with '_Test', but this wont be available in excel 2. In this case we have to compare A1_Test with A1 and show as 100%. 

I tried to add a new column, trimmed '_Test' part and tried to check with entries in Excel 2. But it is not working as expected, it is considering A1_Test and A1 as A1. Need to display A1 and A1_Test as seperate entries in output. Any suggestions will be highly appreciated.

Thanks.

Excel 1			Excel 2
App	Columns			App	Columns
A1	C1			A1	C1
A1	C2			A1	C2
A1	C3			A1	C3
A2	C4			A2	C4
A2	C5			A2	C6
A1_Test	C1
A1_Test	C2
A1_Test	C3

Expected Output

App	% of Columns Aligned?
A1	100
A2	50
A1_Test	100

[Syndicate_Admin]

Hello everyone I have to do a categorization but this categorization depends on the value of a column

Color =

WHERE i = 'DAX measurement'[Days without weeding]

RETURN

SWITCH(TRUE(),

ISBLANK(i),"white",

i<11, "#68896B",

i<15, "#D2D70E",

"#E0052D")

I have this measurement, it works correctly, but I need that when I filter a place it shows me that, and if I filter another place it returns the same measure but with other parameters

[Anonymous · Accepted solution]

HI @Anonymous,

I'd like to suggest you extract the value list based on the current category and use INTERSECT to get intersection items. Then you can use these variables to calculate the percent.

measure =
VAR currCategory =
    SELECTEDVALUE ( Table1[App] )
VAR _list1 =
    CALCULATETABLE (
        VALUES ( Table1[Column] ),
        FILTER ( ALLSELECTED ( Table1 ), [App] = currCategory )
    )
VAR _list2 =
    CALCULATETABLE (
        VALUES ( Table2[Column] ),
        FILTER ( ALLSELECTED ( Table2 ), [App] = currCategory )
    )
VAR _intersect =
    INTERSECT ( _list1, _list2 )
RETURN
    IF (
        _list1 <> BLANK ()
            && _list2 <> BLANK (),
        DIVIDE ( COUNTROWS ( _intersect ), COUNTROWS ( _list1 ), -1 )
    )

Regards,

Xiaoxin Sheng

[lbendlin]

Why is A1_Test  set to 100% and not 0%?

[Anonymous]

A1_Test should be considered as an extension of A1. In future there could be entries like A1_Dummy, A1_Sample etc. In all these cases we should ignore the text after A1 and compare with A1 in Excel 2.