Forum Discussion

neees78's avatar
neees78
Icon for Helper II rankHelper II
4 years ago
Solved

Dax to select or filter visuals

Hello All,

Based on this sample table 

I wanted to  run select query  or filter (not sure )  on  8th March which will give me list of Teams leaving Today  & coming tomorrow

 

I would know they are leaving today because they were ON today but OFF tomorrow

Same for coming – they were OFF Today and ON tomorrow

 

hope its clear & simple

Thanks 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  neees78 ,

    Here are the steps you can follow：

    1. Create measure.

    Teams leaving =
var _select= SELECTEDVALUE('Table'[Date])
var _current=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select&&'Table'[Name]=MAX('Table'[Name])))
var _tomm=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select+1&&'Table'[Name]=MAX('Table'[Name])))
return
IF(
 _current="ON"&&_tomm="OFF",1,0)
    Coming tomorrow =
var _select= SELECTEDVALUE('Table'[Date])
var _current=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select&&'Table'[Name]=MAX('Table'[Name])))
var _tomm=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select+1&&'Table'[Name]=MAX('Table'[Name])))
return
IF(
 _current="OFF"&&_tomm="ON",1,0)

    2. Put [Teams leaving], [Coming tomorrow] in the Filter of each visual and set is =1 .

     

    3. Result:

     

    Best Regards,

    Liu Yang

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    neees78 , Try a measure like

     


    Measure =
    var _1 = countx(filter(allselected(Table), Table[Name] = Max(Table[Name]) && Table[Date] = Today() && Table[Status] = "ON"), Table[Name])
    var _2 = countx(filter(allselected(Table), Table[Name] = Max(Table[Name]) && Table[Date] = Today()+1 && Table[Status] = "OFF"), Table[Name])
    return
    countx(filter(values(Table[Name]), not(isblank(_1)) && not(isblank(_1))), [Name])

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi neees78 ,

     

    Please try this:-

    Teams leaving Today =
CALCULATE (
    DISTINCTCOUNT ( 'Table (4)'[name] ),
    CALCULATETABLE (
        VALUES ( 'Table (4)'[name] ),
        (
            'Table (4)'[Rotation] = "ON"
                && 'Table (4)'[date] = TODAY ()
        ),
        CALCULATETABLE (
            VALUES ( 'Table (4)'[name] ),
            (
                'Table (4)'[Rotation] = "OFF"
                    && 'Table (4)'[date]
                        = TODAY () + 1
            )
        )
    )
)

     

    Coming Tomorrow =
CALCULATE (
    DISTINCTCOUNT ( 'Table (4)'[name] ),
    CALCULATETABLE (
        VALUES ( 'Table (4)'[name] ),
        (
            'Table (4)'[Rotation] = "OFF"
                && 'Table (4)'[date] = TODAY ()
        ),
        CALCULATETABLE (
            VALUES ( 'Table (4)'[name] ),
            (
                'Table (4)'[Rotation] = "ON"
                    && 'Table (4)'[date]
                        = TODAY () + 1
            )
        )
    )
)

     

    Thanks,

    Samarth

    • neees78's avatar
      neees78
      Icon for Helper II rankHelper II
      4 years ago

      Hi Samarth_18  , thanks for your post , just tried this measure , result is counted total not list of names 

      i might be doing something wrong but code show no error 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  neees78 ,

    Here are the steps you can follow：

    1. Create measure.

    Teams leaving =
var _select= SELECTEDVALUE('Table'[Date])
var _current=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select&&'Table'[Name]=MAX('Table'[Name])))
var _tomm=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select+1&&'Table'[Name]=MAX('Table'[Name])))
return
IF(
 _current="ON"&&_tomm="OFF",1,0)
    Coming tomorrow =
var _select= SELECTEDVALUE('Table'[Date])
var _current=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select&&'Table'[Name]=MAX('Table'[Name])))
var _tomm=CALCULATE(MAX('Table'[Rotation]),FILTER(ALL('Table'),'Table'[Date]=_select+1&&'Table'[Name]=MAX('Table'[Name])))
return
IF(
 _current="OFF"&&_tomm="ON",1,0)

    2. Put [Teams leaving], [Coming tomorrow] in the Filter of each visual and set is =1 .

     

    3. Result:

     

    Best Regards,

    Liu Yang

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

  • neees78's avatar
    neees78
    Icon for Helper II rankHelper II
    4 years ago

    amitchandak  not sure this worked - when I created a measure , error reading "expression refers to multiple columns , can't be converted to scalar value"
    my result should list of names , can this be achieved using measure ?