SparkDataGuru Tracker

Re: Two Date Slicers: Setting up pre-selected dates and custom calendar in same report

SparkDataGuru — Tue, 03 Dec 2024 20:06:12 GMT

Thanks Danextian, I watched the entire video and appreciate the very thorough walkthrough. Was able to create a similar setup with the preset and date slider, and also successfully have the date slider update when the preset was utilized.

Just wondering if when changing the date via the date slider, is there a way to automatically have the preset slicer change to 'Custom'?

Two Date Slicers: Setting up pre-selected dates and custom calendar in same report

SparkDataGuru — Tue, 03 Dec 2024 01:18:30 GMT

I'm currently having trouble setting up PowerBI reports to utilize both Custom Date Range Slicers (Such as pre-selecting from YTD, QTD, MTD, etc) and also combining it with the flexiblity of a Custom Date Selector/Slider.

When reviewing more recent tutorials online I've noticed it seems reports can either come in the form of pre-selected date ranges or just using the date selector slider but never both at the same time!

In some older videos I've seen on Youtube (such as this one from 3 years ago: https://www.youtube.com/watch?v=fKygF7VEJnQ), there used to be ways to disable the custom date selector/slider when a user is using the pre-selected date ranges, and to re-enable it when the user selects 'Custom'. However, it seems with the most recent version of PBI, the filter method no longer actually disables the date selector slider....

In my screenshot below, when selecting the pre-selected date ranges (MTD, QTD, YTD), it updates all my visuals but fails to update the proper dates on the calendar slider and vice-versa.

Any suggestions or resources on how I can get both functioning in a report? It's a requirement for the user to be able to quickly select preselected date ranges, but also revert back to a custom date when needed.

Matrix - Multiple Rows - Different Totals

SparkDataGuru — Wed, 01 Mar 2023 01:19:48 GMT

I'm currently struggling with this one detail within my Matrix table. In my Matrix table I have multiple rows consisting of these rows. The requirement for the report is to have a subtotal for the Lead Source Group AND a grand total for Dealership.

Here is what is currently returning in my matrix table. In the green the totals are correct for the total 'Lead Source Group' but it fails to add properly in the total 'Dealership' value, which should be '112' instead of '108'.

'MTD Leads' field is a complex measure comprised of many other measures; now I understand why it does this total as it's technically correct doing the calculations in context to Dealership level instead of the typical Lead Source Group.

The formula for 'MTD Leads' is:

Display_Reallocated_Count_LeadInPeriod_New_Leads = sumx(VALUES(view_LeadSourceGroupName[LeadSourceGroupName]), [Reallocated_Count_LeadInPeriod_New_Leads])

Which when further broken down you'll find the pre-sumx statement as:

Reallocated_Count_LeadInPeriod_New_Leads = [Count_LeadInPeriod_New_Leads]+([Count_LeadInPeriod_Unknown_Leads]*[Percentage_LeadInPeriod_New_Leads])

Typically when there is only one matrix row I know we can use SUMX to get the desired total context. However, I'm stumped as how to get different totals for a single measure like in the desired screenshot above when we have multiple rows in a matrix table...

How do I get a matrix table to reflect the correct total on the 'Lead Source Group' total AND the 'Dealership' in the mentioned desired manner (54+58 = 112)?

As always, thank you for your help!

Re: Error Returned: A table of Multiple Values was supplied where a single value was expected

SparkDataGuru — Fri, 10 Feb 2023 23:26:49 GMT

Looks like I found the answer to my own question. Needed to utilize both a SELECTEDVALUE and SWITCH statement instead of an IF statement:

Decided_LeadInPeriod_New_Leads_Goal =

SWITCH(

SELECTEDVALUE('Manual Goals - POWER BI DATA'[Goal Mode - New Vehicles]),

"Manual",'Manual Goals - POWER BI DATA'[Sum_NewVehiclesManualGoal],

"Automatic", Automatic_Goal_Measure[AutomaticGoal_Forecast_New_Leads]

)

Found by viewing this question below:

https://community.powerbi.com/t5/Desktop/IF-STATEMENT-USING-SELECTED-VALUE/td-p/2276166

Error Returned: A table of Multiple Values was supplied where a single value was expected

SparkDataGuru — Fri, 10 Feb 2023 20:25:04 GMT

Currently we have a Matrix table with a measure which determines what type of 'Goal' to use (between 'Manual' and 'Automatic') the objective is to return in the 3nd column 'Goal Returned'. Depending on whatever Goal Mode is set on the 2nd column will determine the output of the 'Goal Returned'

For the 'Goal Returned' measure I've written this in DAX:

Decided_LeadInPeriod_New_Leads_Goal = IF(VALUES('Manual Goals - POWER BI DATA'[Goal Mode - New Vehicles])="Manual", [Sum_NewVehiclesManualGoal], Automatic_Goal_Measure[AutomaticGoal_Forecast_New_Leads])

Strange enough when I just filter for one store it will return me actual results as intended, it follows my if statment perfectly.

However, if there is more than 1 store in the matrix table, the entire table fails and returns me this error....

Does anyone know how I can implment the intended logic above without it returning an error? Any suggestions on how this can be solved? Thank you!

Creating Measure Which Takes Top Matrix Row Context and Repeats Value

SparkDataGuru — Thu, 09 Feb 2023 21:09:56 GMT

I've been stumped with this for a while... currently I have a matrix table composed of two rows Store Name split into Departments.

In the 'Prev Year Leads' column is actually a measure comprised of other measures. I'm looking to create another Measure 'Prev year Master Leads' which is able to return just that grand total of the 'Prev Year Leads' in context to Store Name, so it would essentially repeat the '193'. Any suggestions in how to tackle this in DAX?

Any help would be appreciated!

Re: Incorrect Sum on Matrix Table - Need Top Summary Line to Sum Instead.

SparkDataGuru — Thu, 09 Feb 2023 00:38:02 GMT

Thanks Ashish, looks like that revised measure works!

Re: Shape Map - Incorrectly Displaying

SparkDataGuru — Wed, 08 Feb 2023 22:44:26 GMT

I got it down to around 5% and it loaded. Appreciate your help!

Incorrect Sum on Matrix Table - Need Top Summary Line to Sum Instead.

SparkDataGuru — Wed, 08 Feb 2023 22:46:58 GMT

Currently been running into an issue with this Matrix table below.

We have a store and it's subdepartments.

The formula to 'Recalculated New Leads' is taken by adding the Raw Leads + Partial Unknown Leads (based on the ratio of Raw new to Used leads). All the components for the formula can be found on column 2 to 5.

It seems each row of this table is correct when taken into isolation for the calculations. However, we're having trouble getting the the top bold line to sum correctly based on the sub-departments. How should we go about in DAX to get the Store Level row to sum rather than do the formula utlized in the sub-department lines?

Thanks for your help!

Re: Shape Map - Incorrectly Displaying

SparkDataGuru — Wed, 08 Feb 2023 19:59:52 GMT

Thank you Danyel! I tried the file you provided and simplified it. Its displaying properly on Power BI now!

I do have a question regarding simplifying, I'm not too familiar with mapping tools like these, on MapShaper for simplifcation, which settings boxes did you tick off for 'Simplification' and 'Method' (Douglas-Peucker, Visvalin Effective Area, Visvalingam Weighted Area, etc) , also how much % did you lower it to for Power BI to process it properly?

Shape Map - Incorrectly Displaying

SparkDataGuru — Wed, 08 Feb 2023 17:21:13 GMT

Recently I downloaded the shapefile for FSA boundaries for Canada found on the Government of Canada's site (https://www12.statcan.gc.ca/census-recensement/2021/geo/sip-pis/boundary-limites/index2021-eng.cfm?year=21).

After I converted the shapefile to TopoJSON using MapShaper (https://mapshaper.org/) as recommended by the Microsoft's support page (https://learn.microsoft.com/en-us/power-bi/visuals/desktop-shape-map) regarding Shape Map utilization.

Initially I tried using the biggest file, it seemed too detailed to properly load on my Power BI Desktop (actually crashed my program), so I used a simplified file (using MapShaper's simplify tool) and added the file to Power BI again at a much smaller file size. It's now displaying however, the image is glitched/incorrect, with verticies converging to the bottom left corner of the box... anyone have recommendations on how I can properly get Canada's FSA boundaries to properly display?

Relative Dates For Automated Reporting

SparkDataGuru — Tue, 20 Dec 2022 21:01:23 GMT

I have a report which has data uploaded daily up to month to date prior (for example: if today is Dec 20, 2022, I have data from Dec 1 to 19, 2022). Currently we are utilizing 'Filters on all pages' to filter the 'Month Year' to a 'relative date is this month'. This works fine for all days until Jan 1, 2023...

How can I ensure that the report will show Dec 1 to 31, 2022 when the calendar date is Jan 1, 2023?

Thank you for your help!

Re: Need Help - Matrix Table Row Field Not Showing from Inactive Relation - Two Calendar Date Comparison

SparkDataGuru — Tue, 06 Dec 2022 22:20:52 GMT

Thank you Amit! Great video series you have going on your channel! You just gained another subscriber 😁

Doing it your way works at the video time 6:26: https://youtu.be/WSeZr_-MiTg?t=386

By setting the 'Lead Count Comparison' to CALCULATE for 'Lead Count' 'with the FILTER being the maxx and minx reflective of the ALLSELECTED of the 'Calendar Comparsion' table, I was able to return all my lead sources as well as compare between two different date ranges!

Follow up question: Isolated Secondary Date Table vs Inactive Relationship Secondary Date Table

I do have an additional question: I also watched your previous video "Power BI Abstract Thesis Episode 18"

https://youtu.be/44fGGmg9fHI?t=489 . At video timing 8:09, you mentioned that your secondary date table (Date2) does not have a relationship with the main table, it is isolated.

I'm quite new to Power BI, I was wondering if I were to establish an inactive relationship for my secondary date table to main table, what impacts would that have when I'm building a comparsion matrix/table? Would this impact my comparison measures in any way (inactive realtionship vs isolated table)?

Need Help - Matrix Table Row Field Not Showing from Inactive Relation - Two Calendar Date Comparison

SparkDataGuru — Tue, 06 Dec 2022 00:13:48 GMT

Currently I have a matrix table which is supposed to compare Lead Sources between two different calendar ranges for lead count. I have two seperate calendar tables with relationships to the main table. The 'Calendar' table has an active relationship and the secondary 'Calendar_Comparison' table has an inactive relationship with the main table ' ls2yrs'.

All the columns within my matrix table are properly displaying based on two dates. However, I'm having issues with the 'LeadSourceName' rows of the matrix table not showing all values that exist in both date ranges! In my data set the two different date ranges selected will not always have the same lead sources. For example:

'Calendar' Date Range (Active Relationship with main table): Jan 1, 2000 to Jan 5, 2000 contains lead sources:

Lead Source : Lead Count

- A lead : 1

- B lead : 2

- C lead : 1

'Calendar_Comparison' Date Range (Inactive Relationship with main table): Jan 6, 2000 to Jan 10, 2000 contains lead sources:

Lead Source : Lead Count

- A lead : 1

- B lead : 3

- C lead : 1

- D lead : 2

In this example above, the matrix table will seem to only show rows relating to lead sources which are present from the active relationship calendar table. It is missing 'D lead' which is present only on the inactive relationship 'Calendar_Comparison' date range table.

Matrix Table

Lead Source : Lead Count : Lead Count Comparison

A lead : 1 : 1

B lead : 2 : 3

C lead : 1 : 1

D lead : 0 : 2 ***(THIS ROW IS MISSING FROM MATRIX TABLE)***

How should I reconfigure my report so I can get D lead to also show up for my date comparision matrix table?