Having problems to add slicer while using date table

[edgarMunoz]

Hi all,
I have a data set (named result) that includes 2 sets of dates:
- Start and end date of an asset

- Start and end when the asset was allocated

Each record in the data is the snapshot of when an asset was activated and when it was allocated to an entity. The entity has a three leves hierachy: Entity --> Sub-entity --> Area.

I created a data table with a specific start date and the end date is the most current date an asset become active (field: assetStartDate in the result table). In the data table I have 5 columns with calculations using as source the result table.

In simple terms, I want to create a visualization like the one below:

The series in the chart come from the date table. The slicer with the dates is from the date table. The slicer with the entity name comes from result table.

The problem I'm facing is I haven't been able to make the slicer called entityName to change the chart. At the moment, there is no relationships between the date table and the result table. I've tried to create the relationships between the tables (one active and mulitple inactive) but since the result table have 4 different columns with dates that impact the calculations, managing the active/inactive relationships between tables has not worked, and even when I manage to handle this, due to the relationships between tables the outcome of the calculations changes. I have iterated between single/both directions, etc. But no matter what I have tried, I cannot make the slicer with the entity name to filter the chart. 

The expected outcome is to have a slicer with the date that shows periods of time (already achieved) and also have a slicer that allows user to see the information in the chart by entity (not achieved).

I have a file with the example but it seems there is no option to attach files.

[v-veshwara-msft · Accepted solution]

Hi @edgarMunoz ,

Thanks for reaching out to Microsoft Fabric Community and for sharing the file and details.

The reason the entity slicer wasn’t affecting your chart is that the chart is based on a disconnected date table, so slicers from the result table don’t propagate. To achieve the expected behavior, the measures need to explicitly reference both the date and entity filters.

You can create a bridge table

EntityBridge = DISTINCT(result[entityName])

with unique entity names and use the entity slicer from this bridge table. This ensures the slicer doesn’t conflict with the date table or other relationships.

Measures like NewAssets and LostAssets can then be defined using SELECTEDVALUE(dateTable[Date]) for the current date in the chart and filtering result by the selected entity from the bridge table.

For example, the NewAssets measure

1NewAssets_SelectedEntity = 
VAR dateTarget = SELECTEDVALUE(dateTable[Date])
RETURN
CALCULATE(
    COUNTROWS(result),
    FILTER(
        result,
        result[assetsStartDate] = dateTarget &&
        (
            ISBLANK(SELECTEDVALUE(EntityBridge[entityName])) ||
            result[entityName] = SELECTEDVALUE(EntityBridge[entityName])
        )
    )
)

counts rows where the asset start date matches the selected date and the entity matches the slicer selection, while the LostAssets measure

1LostAssets_SelectedEntity = 
VAR dateTarget = SELECTEDVALUE(dateTable[Date])
RETURN
- CALCULATE(
    COUNTROWS(result),
    FILTER(
        result,
        result[assetEndDate] = dateTarget &&
        (
            ISBLANK(SELECTEDVALUE(EntityBridge[entityName])) ||
            result[entityName] = SELECTEDVALUE(EntityBridge[entityName])
        )
    )
)

counts rows where the asset end date matches the date.

With this setup, the date slicer from the date table and the entity slicer from the bridge table both work dynamically, and the chart displays the correct per-date bars and line series as expected. You can apply the same pattern for any other calculated measures in the visual.

Please find the .pbix for reference.: Having problems to add slicer while using date table.pbix - Google Drive

Hope this helps. Please reach out for further assistance.

Thank you.

[edgarMunoz]

Thanks for your help!!!
This is what I needed.

[Praful_Potphode]

HI @edgarMunoz ,
can you provide sample snapshot of data model and the measures that you created?Please follow below thread for attaching files.

How to Attach Files 

ideally you should create multiple relationship(Active/Inactive) between date and result table.

then create measures using USERELATIONSHIP DAX function inside CALCULATE Function.

This will allow you to filter by Date and ENtity.

Please give KUdos or mark it as resolved once confirmed.

[edgarMunoz]

Hi @Praful_Potphode,

Thanks for your response.

I already tried creating the relationships and using DAX functions (USERELATIOSHIP AND CALCULATE) for teh calculations but I haven't been able to figure out how to manage them and make sure the calcualtions are not impacted.

See below the link to the file with the current state. The table result is the origin table and the one called dateTable includes the columns with the calculations used by the chart. 

https://drive.google.com/file/d/1WaBhrkZVagBDMhrJ2yvIfh8pVdIZigTj/view?usp=sharing

[Praful_Potphode]

Hi @edgarMunoz 

you can try below approach

create relationship as shown below.

Then you can create below measures and try to create your visual.

assets = COUNT(result[assetId])

New Assets = CALCULATE([assets],USERELATIONSHIP(result[assetsStartDate],dateTable[Date]))

Max assets = MAX([Lost Assets],[New Assets])

keep the relationship like shown above and try to create measures using teh userelationship function.thats the standard practice.

Let me know if it works.

Please give kudos or mark it as resolved  once confirmed.

Thanks and Regards,

Praful