topic Re: Hide Selection but actually use data in DAX Commands and Tips

Hide Selection but actually use data

jzhao — Mon, 30 Oct 2023 01:57:29 GMT

Hello everyone,

I need help creating a visual that compares the sales of Fiscal Year (FY) and Fiscal Year-1 (FY-1). Currently, I am using a slicer, and the idea is that when I select FY23, the visual should display sales data for both FY23 and FY22. However, what happens is that only the FY23 numbers are displayed, and FY22 appears blank. To have both FY23 and FY22 numbers displayed, I need to select both FY23 and FY22.

Is there a way to customize the visualization so that selecting FY23 will display FY23 and FY23-1, and selecting FY22 will display FY22 and FY22-1?

Thank you.

Re: Hide Selection but actually use data

FreemanZ — Mon, 30 Oct 2023 02:20:25 GMT

hi jzhao ,

Try to feed the FY slicer with an unrelated new table and reference the captured value with SELECTEDVALUE in your measure.

it seems the slicer is both selecting the FY23 and filtering your measures. Blank means there is nothing satisfies both FY22 and FY23.

So try to 1) feed the slicer with an isolated new table; 2) or remove FY filter context in your measure - this could be complicated.

check this for more info:

https://www.daxpatterns.com/parameter-table/

Re: Hide Selection but actually use data

jzhao — Mon, 30 Oct 2023 06:28:34 GMT

Upon further checking, the fiscal year slicer changes the source data used in measures. Is there a way to change its behavior so that instead of selecting data for FY23, it selects data from the beginning to FY23?

Re: Hide Selection but actually use data

FreemanZ — Mon, 30 Oct 2023 06:31:19 GMT

yes, by removing FY filter context in your measure.

how does your measure look like?

Re: Hide Selection but actually use data

Jack007 — Mon, 30 Oct 2023 06:42:21 GMT

My measure calculates sales for FY22

Last year sales =

VAR E =EDATE(MAX('01 Date Table'[Date]),-12)

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

'SALES TABLE',

'SALES TABLE'[01 PostingDate]>=S&&'SALES TABLE'[01 PostingDate]<=E&&'SALES TABLE'[DataSource]="ACT")

Var result =CALCULATE(SUM('SALES TABLE'[Sales RESTATE]),ACTIVE)/'Display unit'[Display unit selection]

RETURN result

When I make slicer equal to FY23, it changes the datasource to FY23, removing all sales data in FY22, resulting in blank value Last year sales

Re: Hide Selection but actually use data

FreemanZ — Mon, 30 Oct 2023 07:08:24 GMT

hi Jack007 ,

try like:

Last year sales =

VAR E =EDATE(MAX('01 Date Table'[Date]),-12)

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

'SALES TABLE',

'SALES TABLE'[01 PostingDate]>=S

&&'SALES TABLE'[01 PostingDate]<=E

&&'SALES TABLE'[DataSource]="ACT"

)

Var result =

CALCULATE(

SUM('SALES TABLE'[Sales RESTATE]),

ACTIVE,

ALL('01 Date Table'[Date])

)/'Display unit'[Display unit selection]

RETURN result

Re: Hide Selection but actually use data

Jack007 — Mon, 30 Oct 2023 07:56:09 GMT

It does not work.

I even tried this:

Last year sales =

VAR E =EDATE(MAX('01 Date Table'[Date]),-12)

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

'SALES TABLE',

'SALES TABLE'[01 PostingDate]>=S

&&'SALES TABLE'[01 PostingDate]<=E

&&'SALES TABLE'[DataSource]="ACT"

)

Var result =

CALCULATE(

SUM('SALES TABLE'[Sales RESTATE]),

ACTIVE,

ALL('01 Date Table'[Date]),

ALL('SALES TABLE'[Date])

)/'Display unit'[Display unit selection]

RETURN result

Re: Hide Selection but actually use data

FreemanZ — Mon, 30 Oct 2023 08:49:03 GMT

could you post some data and let us see how it shall work.

Re: Hide Selection but actually use data

jzhao — Wed, 01 Nov 2023 03:02:45 GMT

Source Data Power Query Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdlLilxJEETRvWgs6AiP/1qE9r+NRhQqUNqZ+iiNPMTgvl+/fvT/+n/Vqv/4+aO39uP3zz+nytPI08zTytPO08nTzdPLU2+44fd3DOhY0DGhY0PHiI4VHTM6dhR2lP4H7CjsKOwo7CjsKOwo7CjsGNgxYkclsUpilcQqiVUSqyRWSaySWCWxArECsQKxArECsQKxArECsQKxArECsQKxArECsQKxArECsQKxArGRTEYyGclkJJORTEYyGclkJJORTAaYDDAZYDLAZIDJAJMBJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWSSL9HASzTwEs0kNpPYTGIzic0kNpPYTGIzic0kNkFsgtgEsQliE8QmiE0QmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEVnJayWklp5WcVnJayWklp5WcVnJa4LTAaYHTAqcFTgucFjgtcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnRU75Yu0ktpPYTmI7ie0ktpPYTmI7ie0ktkFsg9gGsQ1iG8Q2iG0Q2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvETnI6yekkp5OcTnI6yekkp5OcTnI64HTA6YDTAacDTgecDjgdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnQ075Yt0kdpPYTWI3id0kdpPYTWI3id0kdkHsgtgFsQtiF8QuiF0QuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELoi9JPaS2EtiL4m9JPaS2EtiL4m9JPZA7IHYA7EHYg/EHog9EHsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2JPzaqhgTZE0IYK2pBBGzpoQwhtKKENKbShhTbF0KYa2pRDm3poUxBtKqJNSbSpiTZF0aYq2kDsz5H/jRahjDal0aY22hRHm+poUx5t6qPN2PJB66rwyvDq8ArxKvFK8WrxivGq8czx7PEM8izyTPJs8ozyrPLM8uzyDPMs80zzbPOM86zzzPPs8wz0LPRM9OjxHUG+o8h3JPmOJt8R5TuqfEeW7+jyXWG+q8x3pfmuNt8V57vqfFee7+rzXYG+q9B3JfquRt8V6bsqfVem7+r0XaG+q9R3pfo/Ry0iNjx3XwDrn1PlaeRp5mnlaefp5Onm6eXpy93HDb+/Y0DHgo4JHRs6RnSs6JjRsaOwo/Q/YEdhR2FHYUdhR2FHYUdhx8COETsqiVUSqyRWSaySWCWxSmKVxCqJFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYiNZDKSyUgmI5mMZDKSyUgmI5mMZDLAZIDJAJMBJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWAywGSAySCTfIkGXqKBl2gmsZnEZhKbSWwmsZnEZhKbSWwmsQliE8QmiE0QmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEJohNEJsgtpLTSk4rOa3ktJLTSk4rOa3ktJLTAqcFTgucFjgtcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnBU4LnBY4LXLKF2snsZ3EdhLbSWwnsZ3EdhLbSWwnsQ1iG8Q2iG0Q2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvENohtENsgdpLTSU4nOZ3kdJLTSU4nOZ3kdJLTAacDTgecDjgdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnA04HnA44HXLKF+smsZvEbhK7SewmsZvEbhK7SewmsQtiF8QuiF0QuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELohdELsgdkHsJbGXxF4Se0nsJbGXxF4Se0nsJbEHYg/EHog9EHsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2IPxB6IPRB7alYNDbQhgjZU0IYM2tBBG0JoQwltSKENLbQphjbV0KYc2tRDm4JoUxFtSqJNTbQpijZV0QZi398cP49ahDLalEab2mhTHG2qo015tKmPNmPLB62rwivDq8MrxKvEK8WrxSvGq8Yzx7PHM8izyDPJs8kzyrPKM8uzyzPMs8wzzbPNM86zzjPPs88z0LPQM9Gjx//95vhxyzlI8h1NviPKd1T5jizf0eW7wnxXme9K811tvivOd9X5rjzf1ee7An1Xof/+5vh55H+jRcCmTP/9zfHzqEXAplT//c3x04sW4bn7Ajj+OVWeRp5mnlaedp5Onm6eXp6+3H3c8Ps7BnQs6JjQsaFjRMeKjhkdOwo7Sv8DdhR2FHYUdhR2FHYUdhR2DOwYsaOSWCWxSmKVxCqJVRKrJFZJrJJYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgViBWIFYgdhIJiOZjGQykslIJiOZjGQykslIJgNMBpgMMBlgMsBkgMkAkwEmA0wGmAwwGWAywGSAyQCTASYDTAaYDDLJl2jgJRp4iWYSm0lsJrGZxGYSm0lsJrGZxGYSmyA2QWyC2ASxCWITxCaITRCbIDZBbILYBLEJYhPEJohNEJsgNkFsgtgEsQliKzmt5LSS00pOKzmt5LSS00pOKzktcFrgtMBpgdMCpwVOC5wWOC1wWuC0wGmB0wKnBU4LnBY4LXBa4LTAaYHTIqd8sXYS20lsJ7GdxHYS20lsJ7GdxHYS2yC2QWyD2AaxDWIbxDaIbRDbILZBbIPYBrENYhvENohtENsgtkFsg9gGsQ1iJzmd5HSS00lOJzmd5HSS00lOJzkdcDrgdMDpgNMBpwNOB5wOOB1wOuB0wOmA0wGnA04HnA44HXA64HTA6YDTIad8sW4Su0nsJrGbxG4Su0nsJrGbxG4SuyB2QeyC2AWxC2IXxC6IXRC7IHZB7ILYBbELYhfELohdELsgdkHsgtgFsQtiF8ReEntJ7CWxl8ReEntJ7CWxl8ReEnsg9kDsgdgDsQdiD8QeiD0QeyD2QOyB2AOxB2IPxB6IPRB7IPZA7IHYA7GnZtXQQBsiaEMFbcigDR20IYQ2lNCGFNrQQptiaFMNbcqhTT20KYg2FdGmJNrURJuiaFMVbSD2/c3x86hFKKNNabSpjTbF0aY62pRHm/poM7Z80LoqvDK8OrxCvEq8UrxavGK8ajxzPHs8gzyLPJM8mzyjPKs8szy7PMM8yzzTPNs84zzrPPM8+zwDPQs9Ez16/N9vjh+3nIMk39HkO6J8R5XvyPIdXb4rzHeV+a4039Xmu+J8V53vyvNdfb4r0HcV+u9vjp9H/jdaBGzK9N/fHD+PWgRsSvXf3xw/vWjRx3P3+38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sales", "numbers"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"

Measure:

Measure =

VAR E =EDATE(MAX('Table(2)'[Date]),-12)

VAR S = EDATE(E,-12)

VAR ACTIVE =

FILTER(

'Table(2)',

'Table(2)'[Date]>=S&&'Table(2)'[Date]<=E)

Var result =CALCULATE(SUM('Table(2)'[numbers]),ACTIVE,ALL('Table(2)'[Date]))

RETURN result

Measure returns blank: