topic Re: Measure to filter a table with last date values in DAX Commands and Tips

Measure to filter a table with last date values

rmeng1975 — Mon, 12 Feb 2024 10:24:25 GMT

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/2024	Parq	Anual Campanha	Activo		72
07/02/2024	Parq	+ Anual	Activo		13
07/02/2024	Parq	canal +	Activo		825
07/02/2024	Parq	Anual Campanha	Activo		6
07/02/2024	Parq	+ Anual	Activo		313
07/02/2024	Parq	canal +	Activo		19
06/02/2024	Parq	+ Anual	Activo		308
06/02/2024	Parq	+ Anual	Activo		13
06/02/2024	Parq	Anual Campanha	Activo		6
06/02/2024	Parq	Anual Campanha	Activo		71
06/02/2024	Active	canal +		UFE	3
06/02/2024	Active	canal +		APP/ Web	1
06/02/2024	Active	canal +		Box	7
06/02/2024	Unactive	+ Anual		Outros	1
06/02/2024	Parq	canal +	Activo		819
06/02/2024	Parq	canal +	Activo		19
05/02/2024	Active	canal +		UFE	2
05/02/2024	Active	canal +		APP/ Web	2
05/02/2024	Active	canal +		Box	3
05/02/2024	Active	+ Anual		Box	1
05/02/2024	Unactive	Anual Campanha		Outros	1
05/02/2024	Unactive	canal +		Outros	5
05/02/2024	Parq	canal +	Activo		823
05/02/2024	Parq	canal +	Activo		18
05/02/2024	Parq	+ Anual	Activo		314
05/02/2024	Parq	+ Anual	Activo		13
05/02/2024	Parq	Anual Campanha	Activo		6
05/02/2024	Parq	Anual Campanha	Activo		72
04/02/2024	Parq	canal +	Activo		818
04/02/2024	Active	canal +		APP/ Web	1
04/02/2024	Active	canal +		Box	1
04/02/2024	Unactive	+ Anual		Outros	1
04/02/2024	Unactive	canal +		Outros	2
04/02/2024	Active	+ 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/2024	Active	canal +	Box	7
06/02/2024	Active	canal +	APP/ Web	1
06/02/2024	Active	canal +	UFE	3

thanks

Re: Measure to filter a table with last date values

lbendlin — Mon, 12 Feb 2024 00:05:04 GMT

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

Re: Measure to filter a table with last date values

talespin — Sun, 11 Feb 2024 14:10:05 GMT

hi rmeng1975 ,

Please share this measure "[# Ativação]"

Re: Measure to filter a table with last date values

rmeng1975 — Mon, 12 Feb 2024 10:01:57 GMT

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

# Total Activations =

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

Re: Measure to filter a table with last date values

rmeng1975 — Mon, 12 Feb 2024 10:20:07 GMT

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.

Re: Measure to filter a table with last date values

talespin — Tue, 13 Feb 2024 05:34:23 GMT

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 ),

)