How to create a measure in Power Bi dax which is to be related to Date Column in matrix

0
votes

I want to make the following table with such data:

Columns: Dates from the selected week(based on calendar table) in WeekMonth slicer

Subcon: Subcontractor distinct list, created with power query from the Subcontractor values in the data table

Plan and Actual: count of set dates, which were set in the data table which looks like:

siteid, subcontractor, milestone, plan date, actual date

Milestone slicer allows to filter data table for a specific milestone and is linked to datatable[milestone]

WeekMonth slicer allows to filter data table for a specific week, only one week can be displayed in the table, by default the "current week is selected".

Filtering data table using the Relatationships between the Calendar[WeekMonth] and the data table is not possible because it contains two types of dates.

As I understand the DAX measures for Plan and Actual must be designed in a such way so it would refer to the date from the matrix Column. How this can be implemented?

How the table must look like(values are not correct yet!):

enter image description here

1
matrixpowerbidaxmeasure
Welcome to Stack Overflow! Can you add a picture of your data model diagram? It's necessary to see your data tables and their relationships to answer the question. - RADO
Hi! actually no relationships are set here, because it's not clear for me which must be.. It is impossible to set up two relationships from the Calendar table to Data table Plan and Actual dates - Monica

1 Answers

1
votes

"Filtering data table using the Relatationships between the Calendar[WeekMonth] and the data table is not possible because it contains two types of dates."

It is actually possible to filter multiple date fields by one calendar table in Power BI.

The way it works:

  1. Connect your Calendar table to plan date field;
  2. Connect the same Calendar table to actual date field. Power BI will add this second relationship as "inactive", as indicated by the dotted line;
  3. Normally, when you use your week slicer, it will filter DAX measures by the active connection only (plan date). However, you can tell DAX to filter by the inactive connection instead, using USERELASHIONSHIP function.

Code typically looks like this:

Metric by Plan Date = SUM(TableName[Field])


Metric by Active Date = 
CALCULATE( [Metric by Plan Date], USERELASHIONSHIP(Calendar[Date], TableName[Actual Date])

Here, you first define your DAX measure normally, and it will calculate using Plan Date. Then, you recalculate the same measure using different relations (Active Date).

This article might help you further:

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi