Forum Discussion

streamly's avatar
streamly
Regular Visitor
6 years ago
Solved

Compare 2 Table columns based on filter applied

Hi Everyone!   I have 2 Tables that i want to compare the 2 columns based on the filter/slicer applied.   Table 1: Date Name Email Task 01/25/2020 A Email A Task 1 01/25/2020 A ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi streamly ,

     

    A sample for your reference.

    Measure = 
VAR se =
    SELECTEDVALUE ( 'date'[Date] )
VAR yms =
    YEAR ( se ) * 100
        + WEEKNUM ( se )
VAR ymc =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) * 100
                + WEEKNUM ( 'Table'[Date] ) = yms
        )
    )
RETURN
    IF ( ymc = BLANK (), "N", "Y" )

     

    Pbix as attached.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • streamly's avatar
    streamly
    Regular Visitor
    6 years ago

    Hi Ashish,

     

    Thanks for the reply. I've missed that Table 1 Dates are Week End Date it should look like this.

     

    Table 1:   
    DateNameEmailTask
    01/03/2020AEmail ATask 1
    01/10/2020AEmail ATask 2
    01/17/2020CEmail CTask 3
    01/10/2020BEmail BTask 4
    01/17/2020BEmail BTask 5
    01/03/2020CEmail CTask 6
    • v-frfei-msft's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi streamly ,

       

      A sample for your reference.

      Measure = 
VAR se =
    SELECTEDVALUE ( 'date'[Date] )
VAR yms =
    YEAR ( se ) * 100
        + WEEKNUM ( se )
VAR ymc =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) * 100
                + WEEKNUM ( 'Table'[Date] ) = yms
        )
    )
RETURN
    IF ( ymc = BLANK (), "N", "Y" )

       

      Pbix as attached.

       

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      My solution should still work.  If it does not, then please share that instance.