Alternative Dax for Calculated Column?

[lauriedata]

This calc column works in import mode by not DQ.  Tried to convert to a measure but can't due to mutltiple options within the categories.  Can someone help me find another way to convert this formula so I can use it as a dimension in my x axis?

Period Alias =

    SWITCH(

        v_flt_enrollment[ag_level],

        "DAILY", "DAY " & v_flt_enrollment[fiscal_nbr_in_year],

 "WEEKLY", "WK " & v_flt_enrollment[fiscal_nbr_in_year],

"MONTHLY", "MNTH " & v_flt_enrollment[fiscal_nbr_in_year],

"QTR", "QTR " & v_flt_enrollment[fiscal_nbr_in_year],

        "YEARLY", "FY",

        "CHECK"//     )

)

[Kedar_Pande · Accepted solution]

Try creating a calculated table for your dimension:

Period Alias Dim =
SUMMARIZE(
v_flt_enrollment,
v_flt_enrollment[ag_level],
v_flt_enrollment[fiscal_nbr_in_year],
"Period Alias",
SWITCH(
v_flt_enrollment[ag_level],
"DAILY", "DAY " & v_flt_enrollment[fiscal_nbr_in_year],
"WEEKLY", "WK " & v_flt_enrollment[fiscal_nbr_in_year],
"MONTHLY", "MNTH " & v_flt_enrollment[fiscal_nbr_in_year],
"QTR", "QTR " & v_flt_enrollment[fiscal_nbr_in_year],
"YEARLY", "FY",
"CHECK"
)
)

Then use this table in your visual instead.

@lauriedata

[lauriedata]

Thank you. After the table, I had to create duplicate columns for fiscal nbr and for ag_level in both my tables and merge them in order to join on both fiscal nbr in year and ag_level.  Is there an easier way to do joins on multiple fields?  Many thanks again for your time and efforts.

[MasonMA]

Hi, beside adding a computed column in your SQL view or table, you may also try switching to a 'Composite model'.

When you set the table to Dual or Import, you can create calculated columns because the data is cached locally. This allows your SWITCH + concatenation logic to work exactly as in Import mode.

[KarinSzilagyi]

Hi @lauriedata, did I understand correctly that your original data looks similar to this:

and you are trying to create a chart similar to this one?

You could add the column via SQL - either directly in the source if possible (since I assume that v_flt_enrollment is a view) or via the advanced section in the connection itself:

select
	*,
	CASE ag_level
            WHEN 'DAILY'   THEN 'DAY '   + CAST(fiscal_nbr_in_year AS varchar(10))
            WHEN 'WEEKLY'  THEN 'WK '    + CAST(fiscal_nbr_in_year AS varchar(10))
            WHEN 'MONTHLY' THEN 'MNTH '  + CAST(fiscal_nbr_in_year AS varchar(10))
            WHEN 'QTR'     THEN 'QTR '   + CAST(fiscal_nbr_in_year AS varchar(10))
            WHEN 'YEARLY'  THEN 'FY'
            ELSE 'CHECK'
       END AS Period_Alias
from dbo.v_flt_enrollment

After that you can remove the regular "Navigation" step that would navigate to the table:

Final result:

=> Close + apply

[lauriedata]

I appreciate that.  I was trying to avoid another column in the source by approaching with Dax.  We are using databricks and while I can convert in a query, I have been asked to either create in Dax or request source changes from the developer (i.e. not in a query source).  Not sure why, but too busy to argue.  

[v-menakakota]

Hi @lauriedata 
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @KarinSzilagyi   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team  

[Nabha-Ahmed]

Hi @lauriedata

Create a Measure for Alias (for tooltip/labels)

If you only need it for display:

 

PeriodAliasTable =
ADDCOLUMNS(
SUMMARIZE(v_flt_enrollment, v_flt_enrollment[ag_level], v_flt_enrollment[fiscal_nbr_in_year]),
"Period Alias",
SWITCH(
TRUE(),
v_flt_enrollment[ag_level] = "DAILY", "DAY " & v_flt_enrollment[fiscal_nbr_in_year],
v_flt_enrollment[ag_level] = "WEEKLY", "WK " & v_flt_enrollment[fiscal_nbr_in_year],
v_flt_enrollment[ag_level] = "MONTHLY", "MNTH " & v_flt_enrollment[fiscal_nbr_in_year],
v_flt_enrollment[ag_level] = "QTR", "QTR " & v_flt_enrollment[fiscal_nbr_in_year],
v_flt_enrollment[ag_level] = "YEARLY", "FY",
"CHECK"
)
)

You can use this in data labels, tooltips, or matrix headers, but not directly on the X-axis (measures can’t be used as categorical axes).

Thanks 🌹✨

recieve me KUDO ✨

[lauriedata]

Thank you.  However,  it is for my x axis.