Forum Discussion

RyanHare92's avatar
RyanHare92
Icon for Helper I rankHelper I
3 years ago
Solved

Power BI - Clash Matrix

I have the following dataset:       and I wanted to create a clash matrix in Power BI that looks like this:       My first issue is that, if I put the product in the Rows and Colu...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    NewMeasure=IF(MAX('Table1'[Prod])<>MAX('Table2'[Prod]), COUNTROWS(INTERSECT(VALUES(Table1[ID]),CALCULATETABLE(VALUES(Table1[ID]),'Table1'[Prod]=MAX('Table2'[Prod])))))

    and there is no relationship between the two tables 

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

NewMeasure=IF(MAX('Table1'[Prod])<>MAX('Table2'[Prod]), COUNTROWS(INTERSECT(VALUES(Table1[ID]),CALCULATETABLE(VALUES(Table1[ID]),'Table1'[Prod]=MAX('Table2'[Prod])))))

and there is no relationship between the two tables 

  • RyanHare92's avatar
    RyanHare92
    Icon for Helper I rankHelper I
    3 years ago

    Hi,

     

    This works perfectly if I have two seperate tables. In my current situation, the real data I'm working with has milions of rows of data, so duplicating it would be very ineficient.

    I tried using the following having only one table, but it didn't seem to work:

    NewMeasure 2 = IF(MAX('Table1'[Prod])<>MAX('Table1'[Prod2]), COUNTROWS(INTERSECT(VALUES(Table1[ID]),CALCULATETABLE(VALUES(Table1[ID]),'Table1'[Prod]=MAX('Table1'[Prod2])))))

    The above assumes that I have a second column that is a duplicate of Prod, called Prod2.

    Any thoughts?
  • RyanHare92's avatar
    RyanHare92
    Icon for Helper I rankHelper I
    3 years ago

    Ah! I've got it. I simply just created a calculated table with the two columns that I needed to compare. 

    I can't thank you enough : )