Power BI - Create New Table based on Selected Value and apply filters

[Vineeta]

Hi there,

I have been stuck with this issue for past few days.  I'm trying to create a new table with this piece of code as follows:
I'm trying to achieve the multi touch based on the lead email and for records created on date is greater than user selected campaign createon date from the interface.  Is it true that I cannot use SELECTEDVALUE while creating a new table. How can I solve this problem.

LeadEmailOPP_TPWON=

 --SelectedCampaignName gets the campaign name selected in the slicer

 VAR SelectedCampaignName = SELECTEDVALUE('tsi_campaigndata (2)'[Name])

 --SelectedCampaignName uses CALCULATE to find the ActualDate associated with the slected campaign name

 VAR SelectedCampaignDate = CALCULATE(

    MAX('tsi_campaigndata (2)'[Actual Start Date])

     )

 --The FILTER inside SUMMARIZE then only include records where the Report Created On is >= that start date

 VAR result = SUMMARIZE(

    FILTER(tsi_campaigndata,

    tsi_campaigndata[opportunity.statecodename]="WON"

    && tsi_campaigndata[opportunity.account(preact_billingorganizationid).customertypecodename] <> "Reseller"

    && tsi_campaigndata[Report Created On] >= SelectedCampaignDate

    ),

    tsi_campaigndata[Lead Email],

    "OPP_WON Count",

    DISTINCTCOUNT(tsi_campaigndata[opportunity.opportunityid]))

    RETURN

   result

Thank You

[v-ssriganesh]

Hi @Vineeta,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @Vineeta,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hello @Vineeta,
Thank you for reaching out to the Microsoft fabric community forum.

I’ve reproduced your scenario in Power BI based on the details you shared and I was able to get the expected output as per your requirement dynamically filtering Lead Email based on the selected campaign and only counting opportunities WON after the selected campaign’s start date, excluding Reseller customer types.

Here's what I implemented:

I created two tables (CampaignData and OpportunityData) with a relationship on CampaignID. I added a slicer to select the campaign, then created a DAX measure to dynamically filter opportunities where Report Created On is after the selected campaign's start date. Finally, I used a table visual to display the distinct count of opportunities by Lead Email.

DAX Measure Used:

OPP_WON_Count_After_Campaign =

VAR SelectedCampaignDate =

    CALCULATE(

        MAX(CampaignData[Actual Start Date])

    )

RETURN

CALCULATE(

    DISTINCTCOUNT(OpportunityData[OpportunityID]),

    OpportunityData[StateCodeName] = "WON",

    OpportunityData[CustomerType] <> "Reseller",

    OpportunityData[Report Created On] >= SelectedCampaignDate

)

Expected Output Sample (When "Campaign B" is selected):

For your reference, I’m attaching the .pbix file I used to reproduce your scenario. You can download and explore it to see the full implementation in action.

Thank you, @danextian, @carolinambatist,& @SamsonTruong for sharing your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @Vineeta,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[danextian]

hi @Vineeta 

Calculated tables and columns are updated only when the data is refreshed or when their formulas are modified. They do not respond to slicer selections. If you use SELECTEDVALUE, which returns blank by default, the calculated table will naturally return blank. If you want a dynamic behavior that responds to slicer selections, you need to use measures within visuals.

If you want better, please provide a non-confidential, workable sample dataset that closely represents your actual data (not an image), along with your expected result based on that sample. It does take effort to prepare a good sample, but so does providing effective solutions.

Please refer to this sticky post -https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[carolinambatist]

Olá! 👋

Esse comportamento realmente está ligado à forma como o DAX interpreta os contextos. O SELECTEDVALUE() depende de um contexto de filtro ativo, como os segmentadores da interface visual. Quando você está criando uma nova tabela (tabela calculada no modelo), esse contexto não está disponível, pois a tabela é gerada de forma estática no momento do processamento, e não de forma dinâmica como uma medida.

Por isso, ao tentar usar SELECTEDVALUE() para capturar a seleção de uma campanha, o DAX não consegue reconhecer esse valor – o que pode gerar erro ou retornar um valor em branco.

✅ Como resolver:
Se o seu objetivo é gerar essa análise com base na seleção do usuário (por exemplo, uma campanha específica escolhida por segmentador), o ideal é transformar seu código em uma medida, não uma nova tabela. As medidas respeitam os filtros visuais e funcionam perfeitamente com SELECTEDVALUE().

Agora, se realmente for necessário criar uma tabela física filtrada por uma campanha específica, uma alternativa é substituir o SELECTEDVALUE() por um valor fixo ou criar um parâmetro.

Se precisar, posso te ajudar a reestruturar o código como medida ou montar uma solução alternativa com parâmetro. 😉

[Vineeta]

Thank You for the quick response Samson.
Unfortunalely, it didn't work.  It's returning a (Blank) record where it should be returning 1 for this one selected campaign.  Campaign was done on 3/10/2025 and 1 opp record was created after the campaign date - therefore 1 result should return for this campaign.  Multi Touch based on above filter. 
Thank You

[Ashish_Excel]

Hi,

Would you want to explore a measure based solution instead.  If yes, then share some data to work with, explain the question and show the expected result.  Share data ina  format that can be pasted in an MS Excel file.

[SamsonTruong]

Hi @Vineeta ,

The reason SELECTEDVALUE() cannot be used directly when creating a calculated table in DAX is because calculated tables are evaluated once at model load or refresh time, not dynamically in response to slicer selections like measures are.

An alternative approach to this would be to use a measure instead of a calculated table. Please try the following measure and let me know if this achieves your desired result:

OPP_WON Count = 
VAR SelectedCampaignDate =
    CALCULATE(
        MAX('tsi_campaigndata (2)'[Actual Start Date]),
        ALLSELECTED('tsi_campaigndata (2)')
    )
RETURN
CALCULATE(
    DISTINCTCOUNT(tsi_campaigndata[opportunity.opportunityid]),
    tsi_campaigndata[opportunity.statecodename] = "WON",
    tsi_campaigndata[opportunity.account(preact_billingorganizationid).customertypecodename] <> "Reseller",
    tsi_campaigndata[Report Created On] >= SelectedCampaignDate
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session