Complex Matrix by DAX | Sum of result of row level if else evaluation

[Anonymous]

Hi all,

I have two tables and there is no relationship between these two tables.
1. Output Table which has all the issues that has been reported

2. Calendar Table which has all the periods

I need to create a matrix with outstanding issue for the historical periods. Example if a issue was report in the month of April and resolved on September then each of the month from April to September will have a count for this issue.

Note: The output table has too many rows (more than 8 cr) and impossible to have conditional merger, may be.

I have developed a DAX to create the Matrix, but it is not summing up well. Could you please check what is wrong in the DAX or suggest alternative solutions?

The DAX, I have created:

Measure = 
VAR Period_End_Date = 
    CALCULATE(
        MAXX('Calendar 2','Calendar 2'[End Date])
    )
VAR Found_Date =
    CALCULATE(
        MAXX('OUTPUT',OUTPUT[DATE_FOUND])
    )
VAR Removal_Date =
    CALCULATE(
        MAXX('OUTPUT',OUTPUT[REMOVAL_DATE])
        )
Var Count_OS =
    IF(AND(Period_End_Date >= Found_Date,Period_End_Date < Removal_Date),1,0)

RETURN
CALCULATE(
    SUMX(OUTPUT,Count_OS))

Current Output

What is worng?

1. sum should be 4

2. sum should be 4

3. sum should be1

Sample Data: https://docs.google.com/spreadsheets/d/1c6cl8H4KE-dZPBH76QRHK8TfeOg-qT6T/edit?usp=sharing&ouid=11685...

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Try this.

Measure = 
VAR Period_End_Date = 
    CALCULATE(
        MAXX('Calendar 2','Calendar 2'[End Date])
    )
VAR Found_Date =
    CALCULATE(
        MAXX('OUTPUT',OUTPUT[DATE_FOUND])
    )
VAR Removal_Date =
    CALCULATE(
        MAXX('OUTPUT',OUTPUT[REMOVAL_DATE])
        )
Var Count_OS =
    IF(AND(Period_End_Date >= Found_Date,Period_End_Date < Removal_Date),1,0)
RETURN
    Count_OS

Measure 2 = 
VAR _table = 
    SUMMARIZE('Output','Output'[RDMS_ID],"count",[Measure])
VAR _result = 
    IF(HASONEVALUE('Output'[RDMS_ID]),[Measure],SUMX(_table,[count]))
RETURN
    _result

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

@v-cgao-msft Thank you very much. It works.

[danextian]

Hi @Anonymous ,

Can you please share a sample data of both tables as well as the expected result using that sample data? A link to an excel file will do.  Also, why not create a relationship between the two tables?

[Anonymous]

Hi @danextian , I have added sample data. Relationship not possible as there is no meaningful relationship. If you closely see the DAX you will see the IF statement which evalutes every row in Output table agains every row in Calendar Table. 

Thanks for your time!