I have 2 Date columns in fact table and need to evaluate Boolean value

[Emelie20]

I have a fact table with 2 date columns, (OrderDate and ShipDate).

I would like to be able to get the count of "False" and "True" from Status column and present the output in one view like below.

I was able to achieve the one below:

But I am having challenges appending the ShipDate next to this.

I have looked up how to use "USERELATIONSHIP" but it seems it applies to rows that contain figures.

Status	OrderDate	ShipDate
FALSE	12/25/2021	12/25/2021
FALSE	9/11/2021	9/18/2021
FALSE	8/21/2021	8/21/2021
FALSE	12/11/2021	12/18/2021
TRUE	12/25/2021	12/25/2021
TRUE	11/20/2021	11/27/2021
FALSE	1/22/2022	1/22/2022
FALSE	10/2/2021	10/2/2021
FALSE	12/11/2021	12/25/2021
TRUE	12/4/2021	12/11/2021
TRUE	10/30/2021	11/6/2021
TRUE	1/22/2022	1/22/2022
FALSE	12/4/2021	12/11/2021
FALSE	1/0/1900	1/0/1900
FALSE	1/22/2022	1/22/2022

[amitchandak · Accepted solution]

@Emelie20 , Join both the dates with a date table one join will active another will be inactive

then use date from date on row or matrix

countrows(Table) // for the date joined as active  assume order date

use this measure for inactive join

calculate(countrows(Table) , userelationship('Date'[Date], Table[ShipDate]))

use these measure in visual

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

[Anonymous · Accepted solution]

Hi @Emelie20 ,

From the screemshot with the result you want, I think you want to count days before weekending per category level1:OrderDate and ShipDate, level2: True and False. 1/0/1900 is not in correct date type, please change it to 1/1/1900.

If you want to add OrderDate and ShipDate as category in matrix column, you need to transform your table by Unpivot. In Power Query Editor, select OrderDate and ShipDate columns and use Unpivot.

For reference: Unpivot columns

Create an unrelated weekending table by dax.

WeekEnding = 
VAR _DATE = ADDCOLUMNS(CALENDAR(DATE(2022,01,01),DATE(2022,01,22)),"WEEKDay",WEEKDay([Date]))
RETURN
SUMMARIZE(FILTER(_DATE,[WEEKDay]=7),[Date])

Then create a measure.

Measure = 
VAR _MAXDATEBEFORE =  CALCULATE(MAX(WeekEnding[Date]),FILTER(ALL(WeekEnding),WeekEnding[Date]<MAX(WeekEnding[Date])))
RETURN
CALCULATE(COUNT('Table'[Value]),FILTER('Table','Table'[Value]<=MAX(WeekEnding[Date])&&'Table'[Value]>_MAXDATEBEFORE))+0

Build a matrix.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Emelie20 , Join both the dates with a date table one join will active another will be inactive

then use date from date on row or matrix

countrows(Table) // for the date joined as active  assume order date

use this measure for inactive join

calculate(countrows(Table) , userelationship('Date'[Date], Table[ShipDate]))

use these measure in visual

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