Help with setting up a model - beginner

[alminho]

Hi everyone, 

I have a somewhat cumbersome issue - and I know this might be frustrating to many to read - but I'm genuinely stuck here. PS long post.

Background: Want to see active members on last date chosen in slicer. I have already narrowed it down to that I essentially need to pin down that [customer_number] in table person needs within [start] and [debited until] in another table called subscription_only_memberships. The key between the both tables is a separate identifyer, [member_id].

The only number I can truly trace and validate is the customer_number, otherwise the member_id would be useful...

Any way I've tried to get these numbers correct they just keep failing. In the last two days I've included ChatGPT and CoPilot but honestly both AI:s only lead me so far - generally it ends up being 100 different DAX calculations that all just break the visualizations in the end. 

Any advice? Or any need for more info (relationships, other tables, etc.) needed? I'm getting extremely frustrated since it doesn't seem THAT hard! 

Thanks! 

[v-kpoloju-msft]

Hi @alminho,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @kushanNa, @Ilgar_Zarbali, @Kishore_KVN, for those inputs on this thread. I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[alminho · Accepted solution]

Hi - and thank you! 

The issue has been solved. Unfortunately I had to clean up my dates a lot (debited until lasting until year 2900 for example). 

[v-kpoloju-msft]

Hi @alminho,

Thank you for confirming that the issue has been resolved. Cleaning up the date values especially those extending far into the future was a great step, as such data can often impact visuals and calculations unexpectedly.

We appreciate you sharing your resolution, as it may assist other community members facing similar issues.

Thank you again for using the Microsoft Fabric Community Forum.

[kushanNa]

hi @alminho 

Since you haven’t provided the sample PBIX file,I have created a sample table and scenario. Please review the attached solution and let me know if it helps.

[alminho]

Hi - and thank you! 

The issue has been solved. Unfortunately I had to clean up my dates a lot (debited until lasting until year 2900 for example). 

[Ilgar_Zarbali]

Here’s a clean, reliable pattern to get “active members on the last date selected”:

• Calendar (disconnected)

Calendar = CALENDAR ( DATE(2018,1,1), DATE(2099,12,31) )

Use Calendar[Date] as your slicer (single- or multi-date is fine — we’ll take the max).

• Relationships

* Person[member_id] (1) → (∗) subscription_only_memberships[member_id]

* No relationship from Calendar (keep it disconnected).

• Measure

Active Members (as of last selected date) :=
VAR d =
MAX ( Calendar[Date] ) -- last date chosen in slicer
RETURN
CALCULATE (
DISTINCTCOUNT ( Person[customer_number] ),
KEEPFILTERS (
FILTER (
subscription_only_memberships,
subscription_only_memberships[start] <= d
&& (
ISBLANK ( subscription_only_memberships[debited until] )
|| subscription_only_memberships[debited until] >= d
)
)
)
)

I hope it helps. If so, please give kudoes and accept it as a solution. Thanks

[alminho]

Hi - and thank you! 

The issue has been solved. Unfortunately I had to clean up my dates a lot (debited until lasting until year 2900 for example). 

[alminho]

Thank you - unfortunately this gives me the same issues as before - the visual I add this to (tried with just a card) crashes. "Error fetching data for this visual" Hit "See details" and it says "Error fetching data for this visual An unexpected error occurred (file ", line , function "). 

I don't know if this is relevant, but the customer numbers in some cases include letters and are formatted as Data type Text. 

[Kishore_KVN]

Hello @alminho , your request is not completely clear. Can you please attach any screenshots or Power BI files for reference. 

Thanks.