Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX measures when there are two date columns

Hi All,   I'm facing an issue in creating a measure using 2 date columns in the dataset. Overview of the dataset: Sales DocumentCreated OnDefect typeDefect dateCountry 1 3/1/2021 Internal D...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks a lot!!
    It's working perfectly 🙂

lbendlin's avatar
lbendlin
Super User
4 years ago

Use a calendar table

Connect the date from the calendar table to the "created on" date with an active connection

Connect the date from the calendar table to the "defect date" with an inactive connection.

 

 

Use the DAX function USERELATIONSHIP() in your measures to switch relationships on the fly.

 

 

 

 

"If I use the Defect filter date the defect % should be total defects/orders created on the defect date".

This is unclear. Please show the expected outcome. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    Thank you for reply.I'm able to get the defect % that you mentioned above using the measure that I have created.This gives me how many of the orders logged on 3/1/21 turned out out to be defective - 71.4%
    My real issue is when I select a date from defect type filter, it should give me the % of defect out of all the orders created on the selected defect date.
    Based on created on date - on 3rd March I had 7 orders and 5 were logged as defects

     

    Based on defect date filter :

    Is this kind of calculation possible?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      According to your screenshot, I think "Created On" slicer and "Defect Date" slicer are both built by columns in your data table. When you select 3/1/2021 in your "Defect Date" slicer, Power BI will filter 3/1/2021 in [Defect Date] column, so you will get both 1.

      It is better for you to create an unrelated "Dim Defect Date" table to build the "Defect Date" slicer.

      Dim Defect Date = CALCULATETABLE( VALUES('Table'[Defect date]),'Table'[Defect date]<>BLANK())

      Then create measures to calcualte Defects and Orders Processed.

      Defects =
VAR _Select_DefectDate =
    SELECTEDVALUE ( 'Dim Defect Date'[Defect date] )
VAR _True =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Defect type] <> "Accurate"
                && 'Table'[Defect date] = _Select_DefectDate
        )
    )
VAR _False =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER ( 'Table', 'Table'[Defect type] <> "Accurate" )
    )
RETURN
    IF ( ISFILTERED ( 'Dim Defect Date'[Defect date] ), _True, _False )
      Orders Processed =
VAR _Select_DefectDate =
    SELECTEDVALUE ( 'Dim Defect Date'[Defect date] )
VAR _True =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Created On] = _Select_DefectDate )
    )
VAR _False =
    CALCULATE ( COUNT ( 'Table'[Sales Document] ) )
RETURN
    IF ( ISFILTERED ( 'Dim Defect Date'[Defect date] ), _True, _False )
      Defect % by order date = 
DIVIDE([Defects],[Orders Processed])

      Result is as below.

       

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks a lot!!
        It's working perfectly 🙂