Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with DAX

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  
AppColumns  AppColumns
A1C1  A1C1
A1C2  A1C2
A1C3  A1C3
A2C4  A2C4
A2C5  A2C6
A1_TestC1    
A1_TestC2    
A1_TestC3    

 

Expected Output

App% of Columns Aligned?
A1100
A250
A1_Test100
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    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