Need to help build a control column that finds a bug with DAX

0
votes

Power BI Desktop, DAX I need to help build a control column that finds a bug.

I have three columns: "SN" - serien nr. Data type: text, "MTH" Type data: Whole Number and "Date" Data type: Date.

Each SN has x Mth. Every Mth has just one date. For each SN, it is true that it can not have more Mth at an earlier date.

Example:


enter image description here

I solved it only by counting the help tables in Query Editor, which took a lot of performance.

1
powerbidax

1 Answers

0
votes

I was able to achieve this using the following calculated column:

Control =
    VAR BugSN = Bug[SN]
    VAR BugMth = Bug[Mth]
    VAR BugDate = Bug[Date]
    RETURN CALCULATE(
               MAX(Bug[Date]),
               ALL(Bug), Bug[SN] = BugSN, Bug[Mth] = BugMth
               ) = BugDate

What this says is that if the date in that row is the max for that SN and Mth combination, then TRUE otherwise FALSE.

(I named the table Bug, but you'll need to replace that with whatever your table name is.)