Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a data model:
[IsActive] is a true false column and indicates if the Project was active on the given date.
I want to summarize IsActive into the Dimension Table as a new Column, such that
Project has Activity = IF( IsActive IN {True()}, TRUE(), FALSE() )
In plain English, if the Project has ever been Active then it will be marked as Active (true), if there has never been any Activity, then the Project is 'Not Active' (false). The reason for doing this as a column is I want to be able to use the value as a Slicer and you can't put measures in Slicers, and if you put the Dimension column in a slicer, I'd have to enable bi-directional filtering on the relationship between the the Fact and Dimension tables.
I'm struggling with writing the DAX expression that gets me to the IsActive column so that I can evaulate it in my IF statement. I can 'get' the table using RELATEDTABLE() but I'm having a brain freeze on how to then navigate my variable to get to the column, since RELATEDTABLE does not accept a column arguement with the table name.
Thoughts, suggestions, should I approach this differently?
Solved! Go to Solution.
Well thanks everyone, you gave me something to chew on at least.
@Jihwan_Kim you raised the point loosing any dynamic changes related to date. That is exactly the point. In this case I'm not concerned about date, only if the Project has ever been Active or Not. I have the Fact table at my disposal if its important to drill into granular monthly behavior.
@v-heq-msft your solution works assuming that a given project only has one entry in the Fact Table, or for each entry the value is the same. But it did get me thinking, which was helpful!
In the end my solution looks like this, if the calculated table is empty, then I know there are no 'true' values indicating the project has had some activity at some point in time. Therefore we can safely assume that the project has never been active and use ISEMPTY to retern the appropriate boolean value.
No Activity =
VAR _projactivedays =
CALCULATETABLE(
'Project Active Days Monthly',
KEEPFILTERS('Project Active Days Monthly'[IsActive] = TRUE())
)
VAR _activecolumn = ISEMPTY(_projactivedays)
VAR _result = IF( _activecolumn, TRUE(), FALSE() )
RETURN
_result
Well thanks everyone, you gave me something to chew on at least.
@Jihwan_Kim you raised the point loosing any dynamic changes related to date. That is exactly the point. In this case I'm not concerned about date, only if the Project has ever been Active or Not. I have the Fact table at my disposal if its important to drill into granular monthly behavior.
@v-heq-msft your solution works assuming that a given project only has one entry in the Fact Table, or for each entry the value is the same. But it did get me thinking, which was helpful!
In the end my solution looks like this, if the calculated table is empty, then I know there are no 'true' values indicating the project has had some activity at some point in time. Therefore we can safely assume that the project has never been active and use ISEMPTY to retern the appropriate boolean value.
No Activity =
VAR _projactivedays =
CALCULATETABLE(
'Project Active Days Monthly',
KEEPFILTERS('Project Active Days Monthly'[IsActive] = TRUE())
)
VAR _activecolumn = ISEMPTY(_projactivedays)
VAR _result = IF( _activecolumn, TRUE(), FALSE() )
RETURN
_result
Hi,
I am not sure how your semantic model looks like, but I suggest to try creating field parameters.
In this case, measures can be in the slicer and it can dynamically show the result by changing the date slicer as well.
In my opinion, if you create calculated column, it will not be dynamically showing the result as date slicer changes.
Please check the below picture and the attached pbix file.
You can refere to the below link to find out how to create Field parameters
Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn
Is Active measure: =
VAR _t =
FILTER (
RELATEDTABLE ( project_active_fact ),
MAX(project_active_fact[is_active]) = 1
)
RETURN
IF ( COUNTROWS ( _t ) > 0, "Is Active" )
Is Inactive measure: =
VAR _t =
FILTER (
RELATEDTABLE ( project_active_fact ),
MAX(project_active_fact[is_active]) = 0
)
RETURN
IF ( COUNTROWS ( _t ) > 0, "Is Inactive" )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @rpiboy_1 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Relationship
Demention Table
Fact Table
Create a column in Demention Table
Project has Activity =
CALCULATE(
MAXX(
RELATEDTABLE('Fact Table'),
IF('Fact Table'[IsActive] = TRUE(), 1, 0)
)
) > 0
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |