Forum Discussion

rmeng1975's avatar
rmeng1975
Frequent Visitor
2 years ago
Solved

Measure to filter a table with last date values

https://1drv.ms/u/s!Am7PNkfu1RRdgrp4ixgtjrtJP22N_w?e=86giCO 
The objective is if I don´t filter the date slicer by default the table shows the last day activations (last day = 06/02/2024 and Type ="Active"):

DateTypeNameStateOriginQuantity

07/02/2024ParqAnual CampanhaActivo 72
07/02/2024Parq+ AnualActivo 13
07/02/2024Parqcanal +Activo 825
07/02/2024ParqAnual CampanhaActivo 6
07/02/2024Parq+ AnualActivo 313
07/02/2024Parqcanal +Activo 19
06/02/2024Parq+ AnualActivo 308
06/02/2024Parq+ AnualActivo 13
06/02/2024ParqAnual CampanhaActivo 6
06/02/2024ParqAnual CampanhaActivo 71
06/02/2024Activecanal + UFE3
06/02/2024Activecanal + APP/ Web1
06/02/2024Activecanal + Box7
06/02/2024Unactive+ Anual Outros1
06/02/2024Parqcanal +Activo 819
06/02/2024Parqcanal +Activo 19
05/02/2024Activecanal + UFE2
05/02/2024Activecanal + APP/ Web2
05/02/2024Activecanal + Box3
05/02/2024Active+ Anual Box1
05/02/2024UnactiveAnual Campanha Outros1
05/02/2024Unactivecanal + Outros5
05/02/2024Parqcanal +Activo 823
05/02/2024Parqcanal +Activo 18
05/02/2024Parq+ AnualActivo 314
05/02/2024Parq+ AnualActivo 13
05/02/2024ParqAnual CampanhaActivo 6
05/02/2024ParqAnual CampanhaActivo 72
04/02/2024Parqcanal +Activo 818
04/02/2024Activecanal + APP/ Web1
04/02/2024Activecanal + Box1
04/02/2024Unactive+ Anual Outros1
04/02/2024Unactivecanal + Outros2
04/02/2024Active+ Anual APP/ Web 

I have created a measure give me the correct total but show all dates   when only 06/02/2024 should appear:

 

 

 

 

#Last Date Activations = 
VAR _SelectedDate = 
CALCULATE ( MAX ( Canal[Date] ), 'Canal'[Type] = "Active" )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date] ),
        [# Total Activations],
        CALCULATE (
            [# Total Activations],
            Canal,
             Canal[date] = _SelectedDate
        )
    )

 

 

 

The result shoul be a table like this :
DateTypeNameStateOriginQuantity

06/02/2024Activecanal + Box7
06/02/2024Activecanal + APP/ Web1
06/02/2024Activecanal + UFE3

 

thanks

"DAX help"
  • talespin's avatar
    talespin
    2 years ago

    hi rmeng1975 ,

     

    Please try this.

    Step1 : Create below measure.

    Step2: Set a filter on Table visual as shown in screenshot.

     

    Create this measure

    IsFiltered =
    VAR _IsFilteredDate = SELECTEDVALUE('Date'[Date])
    VAR _IsFilteredCanalDate = SELECTEDVALUE(Canal[Date])
    VAR _LastActivatedDate = CALCULATE([# Last Date Activated], REMOVEFILTERS(Canal))
    VAR _LastDateActivations = [#Last Date Activations]
    RETURN IF(
                ISBLANK(_IsFilteredDate),
                IF(_IsFilteredCanalDate = _LastActivatedDate && NOT(ISBLANK(_LastDateActivations)), 1 , 0 ),
                1
    )
     

     

5 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi rmeng1975 ,

     

    Please try this.

    Step1 : Create below measure.

    Step2: Set a filter on Table visual as shown in screenshot.

     

    Create this measure

    IsFiltered =
    VAR _IsFilteredDate = SELECTEDVALUE('Date'[Date])
    VAR _IsFilteredCanalDate = SELECTEDVALUE(Canal[Date])
    VAR _LastActivatedDate = CALCULATE([# Last Date Activated], REMOVEFILTERS(Canal))
    VAR _LastDateActivations = [#Last Date Activations]
    RETURN IF(
                ISBLANK(_IsFilteredDate),
                IF(_IsFilteredCanalDate = _LastActivatedDate && NOT(ISBLANK(_LastDateActivations)), 1 , 0 ),
                1
    )
     

     

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

     

    Measures cannot return tables, they can only return scalar values.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • rmeng1975's avatar
      rmeng1975
      Frequent Visitor
      2 years ago

      Thanks for the feedback, I know Measures cannot return tables but the purpose is to use the measure inside the table and filter it. Filter bythe last date and is active.

  • rmeng1975's avatar
    rmeng1975
    Frequent Visitor
    2 years ago

    It is in the PBIX shared link. But here it is 

    # Total Activations =
    CALCULATE ( [# Quantity], Canal[Type] = "Active" )