Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
I have a Fact table with client mutations, with eacht mutation having a StartDate and an EndDate. There is a separate Date dimension table. I am working with an SSAS Cube, Visual studio and a live connected Db.
In the report, I want to be able to show the number of clients on a selected date.
So I first created a measure that gives me a value when the selected date is in between the StartDate and EndDate:
CheckDate:= VAR SELECTEDDATE = SELECTEDVALUE(Dim_REF_Date[BK_Date])
RETURN
IF(SELECTEDDATE > MIN(FCT_JGZ[StartDate]) && SELECTEDDATE < MAX(FCT_JGZ[EndDate]); 1; 0)
Then I created a measure to count the number of clients:
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]); 'Fct_JGZ'; [CheckDate]=1)
This gave an error: 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
So I changed this to:
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]); FILTER('Fct_JGZ'; [CheckDate]=1))
Although this formula does not show an error.. in the report, this measure doesn't show anything..?
Am I doing something wrong?
Solved! Go to Solution.
By VS 2017 it seems like you are refering to Visual studio, is that correct? Which version of VS you have should not matter, but you need to check the version of your SSAS installation:
to check the SSAS version go to SSMS -- connect to SSAS instance --> reports -> standard reports --> General and there would be the version for you
But perhaps more important , it does not work in direct query:
https://community.powerbi.com/t5/Desktop/SELECTEDVALUE-not-a-valid-function/td-p/270639
you can rewrite by using the hasonevalue-function
Hi @Anonymous
try this for your measure:
ClientsOnDate :=
VAR SELECTEDDATE =
SELECTEDVALUE ( Dim_REF_Date[BK_Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fct_JGZ'[ClientNumber] );
FILTER (
'Fct_JGZ';
SELECTEDDATE > FCT_JGZ[StartDate]
&& SELECTEDDATE < FCT_JGZ[EndDate]
)
)
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @sturlaws
Thank you for your reply.
However, the formula does not seem to work. In my report, it just says 'can't display the visual'.
It can't seem to resolve the BK_date as Selectedvalue.
Thanks a lot, I had also tried to create my model in pbi deskstop. But again, the formula works in PBI desktop, but not in my live connected SSAS model.
Is SELECTEDVALUE supported for live connections? And if not, are there other ways to do this?
Which version of SSAS are you running?
According to this: https://dax.guide/selectedvalue/ selectedvalue is supported in SSAS 2017 and 2019
I am using VS 2017 - Version 15.8.7
I am also using Power BI for reporting service, version Jan 2019.
I am using SSMS 13.0.5101.9
By VS 2017 it seems like you are refering to Visual studio, is that correct? Which version of VS you have should not matter, but you need to check the version of your SSAS installation:
to check the SSAS version go to SSMS -- connect to SSAS instance --> reports -> standard reports --> General and there would be the version for you
But perhaps more important , it does not work in direct query:
https://community.powerbi.com/t5/Desktop/SELECTEDVALUE-not-a-valid-function/td-p/270639
you can rewrite by using the hasonevalue-function
Thanks, I will try to update my ssas in due time.
For now I made the following measure, which seems to work:
ClientsonDate:=IF ( HASONEVALUE ( 'Date'[BK_Date] );
CALCULATE ( DISTINCTCOUNT ( 'Fct'[clients] );
FILTER ( 'Fct'; 'Fct'[Startdate] <= MIN ( 'Date'[BK_Date] ) && 'Fct'[EndDate] >= MAX ( 'Date'[BK_Date] )))
But this measure also works fine without the IF (HASONEVALUE() part. So I was overthinking the whole thing and just needed to deactivate the connection between the Date and Fact tables.
Hi @Anonymous
ClientsOnDate:= CALCULATE(DISTINCTCOUNT('Fct_JGZ'[ClientNumber]),filter( 'Dim_REF_Date',Dim_REF_Date[BK_Date]>MIN(FCT_JGZ[StartDate]) && Dim_REF_Date[BK_Date]< MAX(FCT_JGZ[EndDate]))\
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/913784
Hi @Anonymous
Thank you for your reply. The formula 'sort of' works, but it only shows the clients who have the exact BK_date that I select.
I then realized I have another problem:
The Date Dimension is connected to the Fact StartDate column (via DateID columns).
So when I use BK_Date in a filter, it logically filters the Fact for only those StartDate's.
I tried to set the connection to 'unactive', but this results in no data showing at all.
I'm not sure how to solve this.
In addition, when I make a copy of my model in Power Bi Desktop (instead of in SSAS), the formula does show the correct result, as long as I set the connection to 'unactive'. Any idea why it does work here and not in my tabular model?
M.Lameijer
In SSAS Model only disconnect date dimension with your fact.
I assume your fact table have start date and end date.
Now come to power Bi and use date dimension is slicer as after/before/Between slicer as per requirement.
I am giving example for after slicer.
Calculate(count(Fact[column]),filter(fact,fact[star_date] < min(dim[Date]) && fact[end_date] > min(dim[Date]))
Just check those less than greater than conditions as per your requirement.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...
When I disconnect the fact and Date dimension the BK_date filter does nothing, whether I select a single date or a range.
Now the two formulas show all clients that exist in the database.
Again, it does work in a local Power BI model, but not in my SSAS model.
I am sorry, but I don't understand.
First, what is a table level filter?
I created the IF-statement as a measure, but it cant be added to a slicer or used as a visual level filter.
Going back to my measure that uses selectedvalue, the error it gives is:
Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable or function name.
Does this give you an idea of why it is not working? (I am working in January 2019 version of PBI for Reporting Server.)
Is selectedvalue even supported in live connection/direct query?
Thanks
sorry tablel level filter means visual level filter.
follow the steps which i have suggested.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |