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
Super 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
    Community 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
    Super User
    6 years ago

    Hi,

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