Probably HASONEVALUE in my code causing unwanted effect? How do I fix this?

[arunbyc]

My following code displays the description of either (a) work or (b) Materials used in a project depending on whether the record is of type "time" or "materials". The column "lookup_id" in this table is linked to the work_dim and Materials_dim dimension tables, and the descriptions are obtained from the related dimension tables,  Description is displayed on individual records for each project. This is working fine.

Problem:

In the Matrix, these detailed records appear under each Project ID. Records at this rolled-up level (i.e. Project Level) should display total hours and total quantity of materials etc. but no descriptions.

But probably because I am using "HASONEVALUE( ) in my code, whenever there is only one record for a project, the description of the detailed (child) record from the related dimension tab;e is also displayed. For projects that have multiple time and Material records, the description is blank, as intended. I want the description to be blank at the Project level no matter how many records are there under a project. How can I fix this?

Code for the measure:

Description =

var x_id =  SELECTEDVALUE(Time_and_Material[lookup_id])

var display_Value = if (

                        HASONEVALUE(Time_and_Material[lookup_id]) &&

                        SELECTEDVALUE(Time_and_Material[lookup_id][type]) = "time" ,

                                calculate(SELECTEDVALUE(work_dim[description]),work_dim[id] = x_id),

                                calculate(SELECTEDVALUE(material_dim[description]),material_dim[id] = x_id)

                        )

    RETURN display_Value

[AlexisOlson · Accepted solution]

Try using ISINSCOPE instead of HASONEVALUE.

[Anonymous · Accepted solution]

Hi @arunbyc ,

I create three tables as you mentioned.

Then I think you can change the DAX code and you can try the codes below.

Description = 
VAR x_id =
    SELECTEDVALUE ( Time_and_Material[lookup_id] )
VAR display_Value =
    IF (
        HASONEVALUE ( Time_and_Material[lookup_id] )
            && SELECTEDVALUE ( Time_and_Material[type] ) = "time",
        CALCULATE ( SELECTEDVALUE ( work_dim[description] ), work_dim[id] = x_id ),
        CALCULATE (
            SELECTEDVALUE ( material_dim[description] ),
            material_dim[id] = x_id
        )
    )
RETURN
    IF ( ISINSCOPE ( Time_and_Material[ProjectID] ), display_Value, BLANK () )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

Try using ISINSCOPE instead of HASONEVALUE.