Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdnewcomb
Frequent Visitor

Date Slicer interaction with Card 1 is good, but is incorrectly showing zero for Card 2

 

BLUF Question: Is there a way to count items that need to be summed for either Card  if they are found in the array requardless of the date ranges selected in the slicer.

Just want to find the Substring within String and return a flag of 1 if it is found.

 

Background Data showing the expected totals for the quarter

Desired PO Dt (2022 Q1): 74

Pr Sub Dt (2022 Q1): 88

rdnewcomb_2-1650422542939.png

The highlighted dates will be of interest below.

 

Once the slicer is engaged only Card 1 shows the correct total for 2022 Q1, Card2 shows sum of zero

Card 1: 74

Card 2: 0

rdnewcomb_3-1650422910166.png

 

-the slicer allows the user to define the date range,

 

-the date selected array is a string of dates as defined by the slicer, getting all values from the Calendar table

Formula: Dates Selected Array = CONCATENATEX(VALUES(TimeDimExtractProcurement[Gen_Date]), TimeDimExtractProcurement[Gen_Date],unichar(10))
 

The PO Flags are found in the array, flagged and displayed in Card 1 as a sum: 74

Formula: Desired PO Issue Selected Flag =

IF(ISBLANK(SEARCH([Desired PO Issue Gen_Date],[Dates Selected Array],1,BLANK())),0,1)

 

The PR Flags are not being found, not flagged but probably displayed correctly in Card 2 as a sum: 0

FormulaPR Submitted Selected Flag = IF(ISBLANK(SEARCH([PR Submitted Gen_Date],[Dates Selected Array],1,BLANK())),0,1)

 

Summary when no filter is on, the correct totals in the first set of pictures is correct (74 & 88).

Once a filter is applied Card 1 show correct sum but even though we mortals can seem some of the dates in PR Sub Date, these date strings when using the formulas can't find the string noted in the green circles on the Array Card and PR Date.

 

There is a relationship between the table of source of these dates and the Calendar Date Table based upon the PO Date. When this relationship between the table is changed so that it relates to the PR Date, then Card 1 goes to zero and Card2 is correctly populated at 88 for the quarter.

 

Question: Is there a way to count items that need to be summed for either Card  if they are found in the array requardless of the date ranges selected in the slicer.

Just want to find the Substring within String and return a flag of 1 if it is found.

 

Thanks in Advance.

 

 

1 ACCEPTED SOLUTION
rdnewcomb
Frequent Visitor

 work around was to create a second table with the source data columns, do the build the calculated fields and creat an active relationship between the new table and the data table based upon the other PR Date. Using the flag where it looked for the substring in the string (aka the array), I then put the flag colum into the card and summed the values.

 

Works like a charm albeit a little dirty in the model.

 

Will wait to see response about the {p2 Measure] before accepting my solution.

 

rdnewcomb_0-1650497816533.png

 

 

 

View solution in original post

4 REPLIES 4
rdnewcomb
Frequent Visitor

 work around was to create a second table with the source data columns, do the build the calculated fields and creat an active relationship between the new table and the data table based upon the other PR Date. Using the flag where it looked for the substring in the string (aka the array), I then put the flag colum into the card and summed the values.

 

Works like a charm albeit a little dirty in the model.

 

Will wait to see response about the {p2 Measure] before accepting my solution.

 

rdnewcomb_0-1650497816533.png

 

 

 

rdnewcomb
Frequent Visitor

@AllisonKennedy

 

I got this working with another method that I will explain below but am interested to explore your method.

 

I tried the Inactive Realtionships (there are are still there) and watched a video on USERELATIONSHIP.

 

Not understanding what you mean by the [P2 Measure], is this my formula looking into the array for the value?

 

Thanks

 

Rob

@rdnewcomb Sorry for the late reply, the [P2 measure] is just your formula to calculate the measure you want for P2, using the active relationship, then you use the USERELATIONSHIP function to change which relationship it uses to calculate that same formula.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@rdnewcomb  You need to create an inactive relationship between the PR Date and then use the USERELATIONSHIP to create the count measure:

 

CALCULATE ( [P2 Measure] , USERELATIONSHIP ( fact[PR DAte], Calendar[date]) )

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.