Measures in different dataset not visible

[J94]

Hello everyone,

I have a sales dataset with some measures. For instances Sales value, which is the sum of the column Sales[Sales]. I also have a MTD measure for sales, and the formule is Sales value mtd = TOTALMTD( [Sales value],  dim_date[Date] )

This dataset is in the online workspace connected to another dataset. When I open this dataset in de Desktop, only the basic measure, Sales value, is working. Even though in this dataset the tables are the same, so also dim_date[Date] is here. 
Can someone explain what's going on, why it doesn't work and how it can be solved? 

[v-hjannapu]

Hi @J94,
Thank you for reaching out to the Microsoft Fabric Forum Community.

It looks like your MTD measure is showing blank because the date table might not be marked properly in the connected dataset.

Even though dim_date[Date] is present, for functions like TOTALMTD to work, Power BI needs to know that this is a proper date table.

You can try this:

• Open the main Sales dataset (where the original measure works).
• Go to the dim_date table, right-click on the Date column.
• Select “Mark as date table” and choose that Date column.
• Save and publish again.

After that, go back to your connected dataset and check if the MTD measure starts working.If still not working, you may need to create the MTD measure again in the connected dataset and try once more.

please go through with the below document hope it may resolve your Issue
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets
https://learn.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Harshitha.

[v-hjannapu]

Hi @J94,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[J94]

hi @v-hjannapu ,

I followed your steps and marked the column dim_date[Date] as a data table. Unfortunalety without luck. I still see just blanks. 
However, when I plot this measure:

Sales value current month = //current month

VAR _Value =

CALCULATE(

    [Sales value],

    ALL(dim_date),

    dim_date[MonthIndex] = 0

    )

RETURN

"Current month: " & FORMAT(_Value, "#,##0,,.0M")

I do see a (correct) result. But I have a blank when I plot a simple TotalMTD measure which I mentioned before...

[v-hjannapu]

Hello @J94,

Thanks for getting back and trying the steps.

Since your custom measure using MonthIndex = 0 is showing correct values, but the TOTALMTD one is still blank  it means the issue might be related to how the visuals or filters are set up, not just the date table marking.

Please check the following once:
Make sure the visual where you are using the MTD measure has proper date-related fields from dim_date, like Date, Month, or Year. Without this, Power BI won’t know which month to calculate MTD for.Confirm that dim_date[Date] has all the dates that match with your Sales data. If the dates are missing or not matching, MTD will return blank.

Make sure there is an active one-to-many relationship from dim_date[Date] to your Sales[Date] column. This is very important for time functions to work.
 You can refer this doc if needed: Troubleshoot relationships
Since you are using a connected dataset composite model, try creating the MTD measure directly inside that model once  sometimes measures from the main model behave differently.

Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Harshitha.

[J94]

Hi @v-hjannapu 

Thanks for your extended reply and suggestions. 
The dim_date[Date] does match with the Sales table dates. The relationship is active. I also plotted in seperate visuals with Date, YearMonth, Year, Month. Without success. And in my original Sales dataset, I can just plot the MTD measure without any date columns.  

Can it be because table with sales is in import mode in the 4 bucket report (see image)? So the lineage (see image in my first message) goes like this: fact_salesinvoices > Sales dataset > 4 bucket

Maybe this can explain it, even though I am not sure why Sale value current month is visible...

[v-hjannapu · Accepted solution]

Hi @J94,
Thanks for followup

It looks like the issue is coming because the Sales table has been imported into the "4 bucket" report. When we use Import mode instead of a Live connection or DirectQuery, Power BI breaks the connection lineage with the original dataset. Because of this, some measures like MTD Month-To-Date or other time intelligence functions may not work properly when used with date fields. This is why your MTD value is only working when you don't add date columns in your visuals.

To fix this, I suggest  these two options:
Use DirectQuery mode and connect to the Sales dataset directly, so the model can understand the relationship and time functions work correctly. Or, if Import mode is required, you may need to re-create the logic manually using DAX filters and calculations.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Best Regards,
Harshitha.

[J94]

Hi @v-hjannapu 

Thanks for your explanation. I've come to the same conclusion. So I am going to use DirectQuery to resolve this issue. 

Thanks again and have a nice day

[J94]

Hi @Greg_Deckler 

I am not seeing an error. It is just blank

[Greg_Deckler]

@J94 Are you seeing an error?